Transaction isolation levels and associated locking practices

This is my first article on getting started

Transaction isolation

Four characteristics of transactions: atomicity, consistency, isolation, and persistence. Atomicity, consistency and persistence are easy to understand. Today we’re going to focus on isolation.

Isolation simply means that each transaction is isolated from each other, you do your thing, he does his thing, and everyone is isolated from each other. However, everyone is operating under the same database, so how isolated is this “isolation”? Is it “no contact”, “temporary quarantine”, or “you are in each other’s skin”? This is where a new concept comes into play: isolation levels.

Transaction Isolation level

Transaction isolation levels are:

• READ-UNCOMMITTED: The lowest isolation level that allows UNCOMMITTED changes to be READ
• READ-COMMITTED: Allows reading of COMMITTED data from concurrent transactions
• REPEATABLE READ: The result of multiple reads of the same field is consistent unless the data is modified by the transaction itself
• SERIALIZABLE: Highest isolation level, fully compliant to ACID isolation level. All transactions are executed one at a time, so that interference between transactions is completely impossible

The default isolation level of MySQL is Repeatable read

Through the actual demonstration, we can understand the isolation levels very well. Related preparations:

MySQL version: 5.7 (select version())

Table:

CREATE TABLE 'student' (' id 'int(11) NOT NULL AUTO_INCREMENT COMMENT' primary key ', 'number' Varchar (20) NOT NULL COMMENT 'primary key ', 'name' VARCHar (20) DEFAULT NULL COMMENT (' id ') ENGINE=InnoDB DEFAULT CHARSET= UTF8MB4 COMMENT=' name ';Copy the code

Initial data:

Id Number Name 1 2021070501Copy the code

Demo tool: Navicat (enable multiple transactions by opening multiple query Windows)

A demonstration:

It can be analyzed from the above demonstration:

1. The result in step2 shows that the name is still “zhang SAN”, indicating that transaction 2 did not read the uncommitted data of transaction 1, so you can exclude the “read uncommitted” isolation level
2. If the default isolation level of MySQL transaction is “read committed”, then step3 has committed transaction 1, the result in step4 should be “lee si”, but the actual result is still “log three”, which confirms from the side that the isolation level of MySQL transaction is “repeatable read” (the results of two reads are log three).

Transaction related locks

Although there is isolation between transactions, what will happen if two transactions operate on the same data? Which transaction is the final outcome? In the absence of transactions, it is easy to understand that which SQL statement is executed last and which data is used. But with transactions, things get complicated, and the order in which the SQL is executed may not be consistent with the order in which the transaction is committed. Such as:

Transaction 1 goes first

UPDATE student SET name = '1' WHERE id = 1;Copy the code

Transaction 2 executes again

UPDATE student SET name = 'student2' WHERE id = 1;Copy the code

Then commit transaction 2 first and then transaction 1. So is the end result a three-one or a three-two? From the point of view of SQL execution order, the result should be “three two”; In terms of transaction commit order, the result should be “Zhang SAN 1”.

And the actual result? The answer is: never!

We can try it in practice: Demo 2:

From demo 2, we can see that Step2 cannot be executed normally, and we need to wait for Step3 to submit. This is the locking mechanism of MYSQL.

Select * from student WHERE id=1; select * from student WHERE id=1; UPDATE student SET name = ‘student2’ WHERE id = 1; UPDATE student SET name = ‘student2’ WHERE id = 1; Transaction 1 commits the transaction and releases the lock. Therefore, there is no inconsistency between the change order and commit order when different transactions modify the same data.

In terms of lock granularity, MYSQL locks can be simply classified as row locks and table locks. Row locking is to lock the row data, table locking is to lock the entire table data. The above example is row locking. In general, rows are locked for updates and deletes when the matching condition is a primary key or index column. Innodb will use table-level locking if the matching field does not have an index. As you can see, proper indexes in our database table design are critical.

Where there are locks, deadlocks are bound to occur. We’ll have a chance to talk more about locks and deadlocks later