Recently, during a production accident with mysql deadlocks, I found that my knowledge of mysql locks, transactions, etc., was too fragmented, so I tried to string them together with a few examples. The idea is to build up a mental “knowledge tree” by constantly asking and answering questions, coupled with “appropriate” metaphors.

One caveat: this blog is not for white, because you need to know about exclusive locks, shared locks, transactions, and most importantly, when locks are added and opened in transactions. This blog post is more about connecting these fragmented pieces of knowledge.

Project environment

Mysql version: 5.7.28-Winx64

OS: win 10

Database script:

DROP TABLE IF EXISTS `demo_user`;

CREATE TABLE `demo_user` (
  `id` varchar(32) NOT NULL COMMENT 'user id',
  `name` varchar(16) NOT NULL COMMENT 'Username',
  `gender` tinyint(1) DEFAULT '0' COMMENT 'gender',
  `age` int(3) unsigned DEFAULT NULL COMMENT 'User age',
  `gmt_create` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Record creation time',
  `gmt_modified` timestamp NULL DEFAULT NULL COMMENT 'Record last modified time',
  `deleted` tinyint(1) DEFAULT '0' COMMENT 'Delete or not',
  `phone` varchar(11) NOT NULL COMMENT 'Phone number'.PRIMARY KEY (`id`),
  KEY `idx_phone` (`phone`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User table';

insert  into `demo_user`(`id`,`name`,`gender`,`age`,`gmt_create`,`gmt_modified`,`deleted`,`phone`) values ('222'.'zzs001'.0.18.'the 2021-12-13 15:11:03'.'the 2021-12-13 09:59:12'.0.'188 * * * * * * 26');
insert  into `demo_user`(`id`,`name`,`gender`,`age`,`gmt_create`,`gmt_modified`,`deleted`,`phone`) values ('111'.'zzf001'0.18.'the 2001-08-27 11:00:11'.'the 2001-08-27 11:00:13'.0.'188 * * * * * * 22');
Copy the code

Dirty read

The preparatory work

Before we talk about dirty reads, let’s start two sessions and change the transaction isolation level to Read Uncommitted. In this case, the initial age of user 222 is 18.

All set. Let’s get started.

What is dirty reading

Dirty reads are changes that have not been committed by another session. Here are some examples:

As you can see, session 2 modified user ID 222 and session 1 read the changes before committing or rolling back the transaction.

The nature of dirty reads is that unfinished writes are split by reads. Therefore, in order to solve dirty reads, it is necessary to make writes undivisible from reads (and, of course, from other writes), which is called atomicity.

How to solve dirty read

So how do you do that? There are two scenarios.

First, add a lock to the read. To ensure atomicity of write operations, session 2 should lock the record with ID 222 from the start of the update operation to the end of the transaction (note, not from the start of the transaction to the end of the transaction), and session 1 should wait until session 2’s transaction ends. In the above example, it would be natural to assume that session 2’s write operation did not have an exclusive lock to cause dirty reads. However, this is not the case by using SELECT * FROM information_schema.innodb_trx; You can see that session 2 has locked record 222, but session 1’s read operation is not waiting. Why? The root cause is that the reads of session 1 are lockless reads, and lockless reads do not need to wait for write operations in uncommitted transaction isolation levels. Therefore, we need to lock the read (both shared and exclusive locks can be used, but for concurrent reads, shared locks are recommended) as follows:

As you can see, session 1 needs to wait until session 2’s transaction ends because session 2’s update operation is not finished yet, thus avoiding the problem of dirty reads. You might wonder, but the actual project doesn’t look like this, right? Right, because we use more of the second option.

In the second scenario, change the transaction isolation level to Read Committed. In the first scenario, reads and writes are serial, however, we want to read and write in parallel without having dirty reads. The requirements are tricky but reasonable, so there is a second option. As follows:

As you can see, session 2’s update operation is not finished, and session 1 reads the same record without producing dirty reads. How do you do that?

Here I say their own understanding, may not be precise. Logically, it is similar to CopyOnWriteArrayList in Java. When the transaction isolation level is committed, no write operation will be performed on the actual record, but the record that needs to be modified will be cached for change. This part of the cache will be flushed into the actual record when the transaction is committed. It does not read the data being modified.

Unrepeatable read

The preparatory work

Before we talk about unrepeatable reads, we can set the transaction isolation level to read uncommitted or read Committed.

What is unrepeatable reading

A non-repeatable read is a transaction in which the same record is read multiple times with different results. Here are some examples:

As you can see, the age of the user whose ID is 222 in session 1 is 18. Before the transaction ends, session 2 changes his age to 19.

The nature of unrepeatable reads is that unfinished reads are split by writes. Therefore, in order to solve the problem of unrepeatable reads, the read operation must be made undivisible by the write operation, that is, the so-called atomicity must be guaranteed.

How to solve unrepeatable reads

So how do you do that? As with dirty data, there are two solutions.

The first option is to add locks to the read. To ensure atomicity of the read operation, session 1 should lock the record with ID 222 from the start of the read operation to the end of the transaction (note, not from the start of the transaction to the end of the transaction), and session 2 should wait until the end of the transaction to execute the write operation. Therefore, we need to lock the read (both shared and exclusive locks can be used, but for concurrent reads, shared locks are recommended) as follows:

As you can see, the write operation of session 2 can be executed only after the transaction of session 1 ends. Before the transaction ends, session 1 does not have an unrepeatable read for several times.

In the second scenario, change the transaction isolation level to repeatable Read. In the first scenario, reads and writes are serial, however, we want to read and write in parallel without having unrepeatable reads. So, there’s a second option. As follows:

As you can see, the read operation of session 1 is not locked, and the write operation of session 2 does not wait, but does not result in unrepeatable reads. How do you do that?

Here I still talk about my own lax understanding. The first time we read a record with id 222, mysql will store this record in the current transaction cache. The next time we read this data, we will fetch it directly from the cache, so we do not need to read the actual record. This type of read is called snapshot read. If read committed writes are copy on write, repeatable reads are copy on Read.

Phantom read

To be continued.

conclusion

The above is just a little reflection on mysql locks and transactions. Because I haven’t read the underlying logic, it’s all abstract. If there are any mistakes, please correct them.

Finally, thanks for reading.

The resources

MySQL lock (table lock, row lock, shared lock, exclusive lock, gap lock)

This article original articles, reproduced please attach the original source link: www.cnblogs.com/ZhangZiShen…