Shulou(Shulou.com)05/31 Report--

This article shows you how to do MySQL transactions and locks simple test, concise and easy to understand, absolutely can make your eyes shine, through the detailed introduction of this article I hope you can gain something.

All along, for MySQL transactions and lock content is a taste, did not spend time to understand, in a look at colleagues troubleshooting a problem aroused my interest, although a long time has passed, but now a brief summary or some harvest.

First we initialize the data, the isolation level of the transaction is still MySQL default RR, the storage engine is InnoDB

>create table test(id int,name varchar(30));

>insert into test values(1,'aa');

Start a session, start a transaction.

Session 1:

[test]>start transaction;

At this point we look at the information in the show processlist and will not see more specific SQL information.

We look at a table related to transactions in another session,Innodb_trx, which actually corresponds to the storage engine MEMORY

[information_schema]>select *from innodb_trx\G

Then execute a statement in Session 1.

select * from test where id=1 for update;

Looking at the information in the transaction table again, we compare the results before and after the two changes, and find that the only difference is trx_lock_structures, which has changed from 0 to 2.

For the meaning of this field, please refer to the official documentation.

https://dev.mysql.com/doc/refman/5.6/en/innodb-trx-table.html

The official interpretation of the field TRX_LOCK_STRUCTs is as follows:

The number of locks reserved by the transaction.

Session 2:

Execution of statements in Session 2 at this point blocks because there is a corresponding lock wait.

select * from test where id=1 for update;

Wait for a while and Session 2 will prompt for a timeout.

[test]>select * from test where id=1 for update;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

This place is associated with a parameter, innodb_lock_wait_timeout, which controls the length of the blocking 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 more classic data dictionaries in MySQL, innodb_lock_waits,innodb_trx,innodb_trx. The three can be combined to find relatively complete blocking information and transaction situations. An SQL provided by the official is as follows:

We call it check_trx.sql for short, and in this scenario we run check_trx.sql and find thread 3573 waiting and thread 3574 blocking it.

At this time, there is a place to pay attention to, that is, through the show engine innodb status to get the results, the red part can be seen that the lock is a table-level lock. This has something to do with the structure of the table.

We can test perfection in another way, such as testing deadlocks.

test deadlock

First add a record to the table test

insert into test values(2,'bb');

In order to eliminate table-level locks, add a primary key to the table test. If you add a primary key in the following way, it doesn't work. It seems that Oracle is used to it. Many ways of thinking need to be copied. There are still many places to pay attention to 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 add a primary key in the following way.

[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

Now let's repeat the deadlock situation.

Session 1:

Start the transaction and update the row 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 check innodb_trx information, only 1 record.

Session 2:

Start the transaction and update the row 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)

At this point there is no blocking between the two, since each affects independent rows.

>source check_trx.sql

Empty set (0.00 sec)

Check the transaction table, there are 2 records inside.

Session 1:

Modify data row id=2 in session 1.

select * from test where id=2 for update;

Looking at the transaction table, there is a blocking message.

Session 2:

Modify the row id=1 in session 2, and you will find that there is a deadlock, and MySQL will not hesitate to clean up the blocked session. This process is done automatically.

[test]>select * from test where id=1 for update;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

If you look at the blocking information, you will find that it has been cleaned up.

[(none)]>source check_trx.sql

Empty set (0.00 sec)

If you look at the transaction table, you will see that there is only one record left.

Overall, MySQL's data dictionary is still relatively small, but it is still relatively clear to use.

The above is how to conduct simple tests of transactions and locks in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.