The official website link: www.mysql.com/

Mapping between mysql and Sequelize data types

// todo

Transaction and Lock

basis

A transaction is a logical unit of database execution consisting of a finite sequence of database operations. These operations wrapped in transactions have a common execution result, either all successful or all failed and all rolled back. Transactions have 4 major features: Atomicity, Consistency, Isolation, and Durability.

Why add transactions

Consistency: When data A is updated, data B and C associated with A must also be changed. If the change fails, all data must be changed back to the original data. Isolation: the inventory of A commodity is 10, inventory +1 after A queries available inventory, and inventory -1 during B queries. There will be A problem with A’s query data. A transaction needs to be added during the operation of B so that the data queried by A is the data that B has modified but is committed. Wait for Bcommit before performing operation A

Issues and isolation levels for parallel transactions

Four problems with parallel transactions:

  1. Update lost: writes separately from other transactions that read the same thing, overwriting their own writes. (Updates are lost to those who write faster)
  2. Dirty read: Read data not committed by another transaction. (Every time the data is rolled, it is dirty and invalid.)
  3. Non-repeatable read: Other transactions are modified between reads.
  4. Unreal read: other transactions added or deleted between reads.

Corresponding Isolation level

  1. READ UNCOMMITTED: no operation is committed.
  2. READ COMMITTED: Indicates that the COMMITTED data is not dirty READ.
  3. REPEATABLE READ: add row lock, no modification between two reads, no dirty READ and no repeat READ;
  4. SERIALIZABLE: Add table lock, all serial, no all problems.

The types of lock

Optimistic lock will not lock the data because it thinks that others will not modify it every time when it goes to get the data. However, it will judge whether others have updated the data during the period of update. It can use the version number and other mechanisms

For example: A and B modify the same record at the same time, suppose the original amount of the database is 100 yuan, A pair of amount +100, B to the database -50, the normal result is 150, but because of concurrency, the result may be 200, or 50 solution: At the same time, A and B read and read the data version 1, the amount of A +100, changed the data version to 2, and submitted the data. At this time, the data version is 1, and the update is successful. B reads the data version 1, and the amount is -50. The result is 50 at this time, and changes the data version to 2. Submits the data, and compares the original database version 2, but the data is not higher than the original version

Pessimistic locks lock data every time they go to get it because they think someone else is going to change it, so every time they go to get it they block it until it gets the lock

Optimistic lock: program implementation, there will be no deadlocks and other problems. His scenario is also relatively optimistic. Prevent in addition to the application of the database operation suit to read more frequently, if there is a large amount of write operation, the data will increase the likelihood of conflict, in order to ensure the consistency of the data, the application layer need to constantly to get the data, it will increase a lot of query operation, reduce the throughput of the system. Pessimistic lock: a database implementation that prevents database writes. This method is suitable for scenarios with frequent write operations. If a large number of read operations occur, locks are added during each read operation, which increases the overhead of locks and reduces the throughput of the system.

Shared lock: also known as read lock. A shared lock means that multiple transactions can share the same lock on the same data. All transactions can access the data, but can only read the data. Lock in share mode: also known as write lock. If a transaction acquires an exclusive lock on a row, other transactions cannot acquire other locks on that row, including shared locks and exclusive locks. If a transaction acquires an exclusive lock on a row, other transactions cannot acquire other locks on that row, including shared locks and exclusive locks. for update

Mysql transaction

Commit the transaction

Roll back the transaction

Sequelize Transaction lock processing

export enum LOCK {
  UPDATE = 'UPDATE',
  SHARE = 'SHARE',
  KEY_SHARE = 'KEY SHARE',
  NO_KEY_UPDATE = 'NO KEY UPDATE',}Copy the code
const transaction = await db.sequelize.transaction({ autocommit: false })
try {
    const component = await Component.findOne({
        where: { id: componentId }, lock: transaction.LOCK.UPDATE, MIT ()} catch (e) {rollback()}Copy the code

Use mysql and Sequelize to add, delete, change, query, and sort groups

The new table

CREATE TABLE table_name (column_name column_type);

CREATE TABLE IF NOT EXISTS `student` ( 
    `id` INT UNSIGNED AUTO_INCREMENT,
    `name` VARCHAR(40) NOT NULL,
    `create_at` DATE,
     PRIMARY KEY ( `id`)
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

sequelize

exportclass Student extends Model { public id! : number public name! : string public value! : string publicreadonlycreated_at! : Date }export default (sequelize: Sequelize) => {
  Student.init(
    {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true.type: DataTypes.INTEGER({
          length: 20,
          unsigned: true,
        }),
      },
      name: {
        type: STRING(40),
        allowNull: false,
      },
      created_at: {
        type: DATE,
        allowNull: false,
        defaultValue: DataTypes.NOW,
      },
    },
    {
      sequelize,
      tableName: "student",
      createdAt: "created_at",
      comment: "Student Table",})return Student
}
Copy the code

Mysql statement executed

insert

mysql

INSERT INTO table_name ( field1, field2,... fieldN ) VALUES ( value1, value2,... valueN );Copy the code

Example Execution results:

sequelize 

Student.create({
    name: name,
  })
Copy the code

The query

  1. WHERE “

  2. LIKE

  3. UNION

Sort ORDER BY

GROUP BY GROUP

Did not finish the spray point error directly released the late continuous update

refrence

  1. Mysql transaction and data consistency processing
  2. Mysql shared lock and exclusive lock