Test the simple usage of transactions and locks in MySQL

For a long time, I have never been familiar with MySQL transactions and locks. I was interested in a problem I encountered when I checked my colleagues' troubleshooting, however, there are still some gains.
First, we initialize the data. The transaction isolation level is the default RR of MySQL. The storage engine is InnoDB.
> Create table test (id int, name varchar (30 ));
> Insert into test values (1, 'A ');
Start a session and start a transaction.
Session 1:
[Test]> start transaction;

At this time, we can view the show processlist information and will not see more specific SQL information.

We can view a transaction-related table in another session, Innodb_trx. In fact, its storage engine is MEMORY.
[Information_schema]> select * from innodb_trx \ G

Then execute a statement in Session 1.
Select * from test where id = 1 for update;
View the information of the transaction table again. We compared the results changes of the previous two times and found that the only difference was the location of trx_lock_structs, which was changed from 0 to 2.

For the meaning of this field, refer to the official documentation.
Https://dev.mysql.com/doc/refman/5.6/en/innodb-trx-table.html

The official explanation of the field TRX_LOCK_STRUCTS is as follows:
The number of locks reserved by the transaction.

YesWords2:
At this time, the execution of the statement in Session 2 will be blocked because of the corresponding lock wait.
Select * from test where id = 1 for update;
Wait for a while, and Session 2 will prompt timeout.
[Test]> select * from test where id = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
This is related to a parameter. innodb_lock_wait_timeout controls the duration of the blocked wait.
[Test]> show variables like '% innodb_lock_wait_timeout % ';
| Variable_name | Value |
| Innodb_lock_wait_timeout | 120 |
For transaction-related information viewing, there are three classic data dictionaries in MySQL, innodb_lock_waits, innodb_trx, and innodb_trx, which can be combined, you can find relatively complete blocking information and transactions. The official SQL statement is as follows:

We call check_trx. SQL for short. In this scenario, when we run check_trx. SQL, we will find that thread 3573 is waiting, and thread 3574 is blocking it.




Note that the red part of the result obtained through show engine innodb status shows that the lock is a table-level lock. This is still related to the table structure.
We can use another method to test the improvement, for example, test the deadlock.

Test deadlock
First, add a record to table test.
Insert into test values (2, 'BB ');
To prevent table-level locks, you can add a primary key to table test. If you use the following method to add a primary key, it seems that Oracle is used to it and many ways of thinking should be copied, pay attention to the SQL syntax.
[Test]> alter table test modify id primary key;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server vline 1...
You can use the following method to add a primary key.
[Test]> alter table test add unique index (id), add primary key (id );
Query OK, 2 rows affected (0.25 sec)
Records: 2 Duplicates: 0 Warnings: 0
Next we will reproduce the deadlock.

YesWords1:
Start the transaction and update the row of data with id = 1.
Start transaction;
[Test]> select * from test where id = 1 for update;
+ ---- + ------ +
| Id | name |
+ ---- + ------ +
| 1 | aa |
+ ---- + ------ +
1 row in set (0.00 sec)
At this time, the innodb_trx information is viewed. There is only one record.


Session2:
Start the transaction and update the row of data with id = 2.
Start transaction;
Select * from test where id = 2 for update;
(Root: localhost: Sat Oct 8 18:15:10 2016) [test]> select * from test where id = 2 for update;
+ ---- + ------ +
| Id | name |
+ ---- + ------ +
| 2 | bb |
+ ---- + ------ +
1 row in set (0.00 sec)
In this case, there is no blocking between the two because they both affect independent rows.
> Source check_trx. SQL
Empty set (0.00 sec)
Check the transaction table, which contains two records.



Session 1:
Modify the data row with id = 2 in Session 1.
Select * from test where id = 2 for update;
The transaction table is blocked.


Session 2:
Modify the data row with id = 1 in Session 2. A deadlock exists, and MySQL does not hesitate to clear the blocked session. This process is automatically completed.
[Test]> select * from test where id = 1 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
View the blocking information and you will find that the information has been cleared up.
[(None)]> source check_trx. SQL
Empty set (0.00 sec)
Check the transaction table and you will find that there is only one record.

The general feeling is that MySQL still has few data dictionaries, but it is clear to use them.

Deadlock principle and analysis

The previous sections in this article basically cover all the locking rules of MySQL/InnoDB. Understanding how to lock MySQL has two important roles:

 

• You can write SQL statements that do not encounter deadlocks based on MySQL locking rules;
   
• You can locate the cause of the deadlock on the egress according to the MySQL locking rules;

Next, let's take a look at two deadlocks (one is two SQL statements of two sessions generate deadlocks; the other is one SQL statement of two sessions, resulting in deadlocks ):

The two deadlock cases above. The first one is very easy to understand and is also the most common deadlock. Each transaction executes two SQL statements and holds a lock respectively, and then adds another lock to generate a deadlock.

 

In the second case, although each Session has only one statement, a deadlock still occurs. To analyze the deadlock, you must first use the MySQL locking rules mentioned earlier in this article. For Session 1, the [hdc, 1], [hdc, 6] read starting from the name index meet the conditions, not only will the record X lock on the name index be added, in addition, the record X lock on the clustered index will be added. The lock sequence is [1, hdc, 100], and then [6, hdc, 10]. Session 2, starting from the pubtime Index, [] and [, 1] all meet the filtering conditions. The record X lock on the clustered index will also be added, and the lock order will be [6, hdc, 10], and later [1, hdc, 100]. No. The lock sequence of Session 1 is the opposite. If both sessions hold the first lock and the second lock is requested, the deadlock will occur.

 

Conclusion:Whether a deadlock occurs is not caused by the number of SQL statements in the transaction, The key to deadlock lies in: Two (or more) sessions Lock orderInconsistent. Using the locking rules of each SQL statement in MySQL, we can analyze the locking sequence of each statement, then, check whether there are locks in the opposite order between multiple concurrent SQL statements to analyze various potential deadlocks and the causes of deadlocks on the egress.