Learn about creating transactions in Sequelize and 🔐

Sequelize is a node.js ORM library that allows you to use familiar JS links to manipulate databases. Work needs, the author’s daily work in addition to cutting diagrams, also began to write back-end code. Due to inexperience, previously developed features failed in concurrent cases. Go back and sort out the questions and take notes.

Basic knowledge of

Transaction: A Transaction is a logical unit of database execution consisting of a limited sequence of database operations. These operations wrapped in transactions have a common execution result, either all successful or all failed and all rolled back. all-or-nothing

Take a bank transfer. User A transfers 100 to user B. Pseudocode (ignore details)

Balance A = balance A - 100 balance B = balance B + 100Copy the code

Account A -100 and account B +100 are two independent statements. If an exception occurs in the middle, resulting in program interruption, account A’s 100 will disappear into thin air. No one wants to see this result, wrapped in transactions to ensure atomicity of the transfer operation. If an error occurs, all are rolled back.

start transaction; // Transfer operationcommit;
Copy the code

Transaction is demonstrated in Terminal.

Sequelize provides a Transaction class that creates transactions from Sequelize.Transaction and sets each database operation to which Transaction the current operation belongs.

await sequelize.transaction({}, async (transaction) => {
    const instance = await Accounts.findOne({
      where: {
        name: 'HelKyle',
      },
      transaction,
    });
    
    await instance.update({
      balances: instance.balances + number,
    }, {
      transaction,
    })
})
Copy the code

As you can see from the Squelize log, the transaction id 444a5AFe-9635-40FD-90d7-10f5AA16077a is created and all subsequent queries and updates are run in this transaction.

Executing (444a5afe-9635-40fd-90d7-10f5aa16077a): START TRANSACTION;
Executing (444a5afe-9635-40fd-90d7-10f5aa16077a): SELECT "name"."balances" FROM "accounts" AS "accounts" WHERE "accounts"."name" = 'HelKyle';
Executing (444a5afe-9635-40fd-90d7-10f5aa16077a): UPDATE "accounts" SET "balances"=The $1 WHERE "name" = $2
Executing (444a5afe-9635-40fd-90d7-10f5aa16077a): COMMIT;
Copy the code

If you don’t want to manually pass a TRANSACTION object every time, you can configure a global default transaction by configuring the CLS.

The tragedy of concurrency

Transactions only solve the problem of atomicity of operations; another thorny problem is concurrency. Suppose that in the process of transfer from A to B, C also happens to transfer 80 to A, and use the form of table to demonstrate the problems that may occur in the concurrent process.

Transaction 1 (Transfer from A to B)	Transaction 2 (Transfer from C to A)
	Query balance 200 of A
Query balance 200 of A
	Update A balance = 200 + 80
Update A balance = 200-100

As can be seen from the result, A did not receive 80 from C, and user C lost 💰. Concurrency problems can be avoided by locking.

The concept of the lock

Pessimistic locks vs. Optimistic locks:

Pessimistic locking is reserved for the outside world. To avoid conflicts, a record is locked first, and other transactions must wait to perform operations on the record before the current transaction is released.

Transaction 1 (Transfer from A to B)	Transaction 2 (Transfer from C to A)
	Query A balance 200 and lock the record
Query the balance of A, find another transaction locked record, wait…
	Update A balance = 200 + 80, release lock
Get enforcement. Check balance A, 280
Update A balance = 280-100

MySql and Postgres both implement pessimistic lock, execute related statement (select for update), no need to develop. The disadvantage of pessimistic locking is that it can affect throughput in scenarios with frequent read operations.

The opposite of pessimism is optimistic locking, which assumes that there are fewer conflicts and that any transaction can read the resource first and make a judgment while writing updates. The version field is usually added and verion + 1 is used for each update. When the update is committed to the database, the version is determined and the version is retried if it has expired.

Transaction 1 (Transfer from A to B)	Transaction 2 (Transfer from C to A)
	Query A balance 200 and version N
Query A balance 200 and version N
	Update A balance = 200 + 80, version number = N + 1
If the latest version is not n, try again
Query A balance 280, version N + 1
Update A balance = 280-100, version number = N + 2

SQL code:

select name, balances, version from accounts where name='HelKyle';

update accounts set version=version+1, balances=balances+100
    where name='HelKyle' and version= # {version}
Copy the code

Optimistic locking can cause retries in scenarios with frequent write operations, which also affects throughput.

Exclusive locks VS shared locks:

Exclusive lock is a pessimistic lock, when the query lock. There can be only one exclusive lock on a resource at a time, and other transactions adding exclusive locks to this record must wait for the current transaction to complete (other transaction reads must wait).

SQL code

select * from accounts where name='HelKyle' for update;
Copy the code

Sequelize writing

await Accounts.findOne({
    where: { name: 'HelKyle' },
    lock: Sequelize.Transaction.LOCK.UPDATE
});
Copy the code

Demo: 👈 transaction does not end when 👉 transaction can only wait until the exclusive lock is released.

A transaction	Transaction 2
start transaction;	start transaction;
select * from accounts where name=’A’ for update;
Output: A 100
	select * from accounts where name=’A’ for update;
	waiting…
commit;
	Output: A, 100
	commit;

A shared lock allows multiple resources to exist at the same time. You can modify or delete a resource only after all other shared locks are released.

SQL code

select * from accounts where name='HelKyle' for share;
Copy the code

Sequelize writing

await Accounts.findOne({
    where: { name: 'HelKyle' },
    lock: Sequelize.Transaction.LOCK.SHARE
});
Copy the code

Demo: 👈 👉 transaction can be queried, 👈 transaction to modify data, because the 👉 shared lock is not released, the modification operation can only wait.

A transaction	Transaction 2
start transaction;	start transaction;
select * from accounts where name=’A’ for share;
Output: A 100
	select * from accounts where name=’A’ for share;
	Output: A 100
update accounts set balances=10 where name=’A’
waiting…
	commit;
set A.balances = 10
commit;

Sequelize.transaction (options) configuration parameter isolationLevel: sequelize.Transaction (options)

level	Dirty read	Unrepeatable read	Phantom read
READ_UNCOMMITTED The read is not committed
READ_COMMITTED Read committed	❌
REPEATABLE_READ Repeatable read	❌	❌
SERIALIZABLE SERIALIZABLE	❌	❌	❌

The ❌ of 👆 indicates that at this level, a certain type of problem does not occur.

Noun analysis:

Dirty reads are when uncommitted data is read in one transaction from another. If the other transaction ultimately fails and does not write to the database, the first transaction gets the data that does not exist.

A transaction	Transaction 2
start transaction;	start transaction;
select * from accounts where name=’A’;
Output: A 100
update accounts set balances=10 where name=’A’
	select * from accounts where name=’A’;
	Output: A 10 (when the transaction is not committed)

Non-repeatable reads describe a transaction that reads a uniform resource multiple times (with no modification in this transaction) and gets different results.

A transaction	Transaction 2
start transaction;	start transaction;
select * from accounts where name=’A’;
Output: A 100
	update accounts set balances=10 where name=’A’
	commit;
select * from accounts where name=’A’;
Output: A 10

Unreal read, refers to the occurrence of the query conditions, but not before 👀. For example, if the sub becomes 0 over all data in a table in queryAll, the sub becomes 0 because other transactions are writing new content at the same time. Therefore, the sub clearly conforms to queryAll but the sub does not become 0, just like 👻.

A transaction	Transaction 2
start transaction;	start transaction;
select * from accounts;
Output: A 100
update accounts set balances=0;
	insert into accounts values (‘B’, 100);
	commit;
commit;
select * from accounts;
Output: A 0, B 100

Configure isolationLevel in Sequelize

sequelize.transaction({
	isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.SERIALIZABLE
}, transaction => {
  // your transactions
});
Copy the code

A link to the

MySQL transaction isolation levels
Optimistic and pessimistic locks

Learn about creating transactions in Sequelize and 🔐

Basic knowledge of

The tragedy of concurrency

The concept of the lock

A link to the

Related Posts

Flex layout beginner notes

The Vue3.0-Composition API takes a new look at the real business

Vue Element nested form validation