Principle test of MySQL InnoDB Engine lock

Principle test of MySQL InnoDB Engine lock

How does MySQL INNODB Engine lock work? Let's test it.

Mysql> select version ();

+ ----------- +
| VERSION () |
+ ----------- +
| 5.5.20 |
+ ----------- +
1 row in set (0.00 sec)

Create table test
(
A INT (5 ),
B VARCHAR (10 ),
C VARCHAR (10)
);
Insert into test VALUES (1, '20160301', '20160301 ');
Insert into test VALUES (2, '20160301', '20160301 ');
Insert into test VALUES (3, '20160301', '20160301 ');
Insert into test VALUES (4, '20160301', '20160301 ');
Insert into test VALUES (5, '20160301', '20160301 ');
Insert into test VALUES (6, '20160301', '20160301 ');
COMMIT;

Mysql> select * from test;
+ ------ +
| A | B | c |
+ ------ +
| 1 | 111 | 111 |
| 2 | 222 | 222 |
| 3 | 333 | 333 |
| 4 | 444 | 444 |
| 5 | 555 | 555 |
| 6 | 666 | 666 |
+ ------ +
6 rows in set (0.00 sec)

Set autocommit = off to complete the experiment in the CMD window;

1. modify a different record without a primary key
Session1:
Mysql> update test set B = '000000' where a = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Session2:
Mysql> update test set B = '000000' where a = 2; -- First hang, an error is reported after a period of time.
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

2. If no primary key exists, add one data entry and then modify another data entry.
Session1:
Mysql> insert into test values (7, '20160301', '20160301 ');
Query OK, 1 row affected (0.00 sec)

Session2:
Mysql> update test set B = '000000' where a = 2; -- First hang, an error is reported after a period of time.
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

3. Modify different records with primary keys.
Alter table test add primary key ();
No full table lock is generated when a primary key exists.
Session1:
Mysql> update test set B = '000000' where a = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Session2:
Mysql> update test set B = '000000' where a = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

If the same record is modified when a primary key exists, it will be hang, indicating that it is a row lock.
Session1:
Mysql> update test set B = '000000' where a = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Session2:
Mysql> update test set B = '000000' where a = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

4. insert and update with primary keys
Session1:
Mysql> insert into test values (8, '20160301', '20160301 ');
Query OK, 1 row affected (0.00 sec)

Session2:
Mysql> update test set B = '000000' where a = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

5. Modify different records without Indexing
Session1:
Mysql> update test set c = '000000' where B = '000000 ';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Session2:
Mysql> update test set c = '000000' where B = '000000 ';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

6. Modify different records with indexes
Create index ind_t_ B ON test (B );
Session1:
Mysql> update test set c = '000000' where B = '000000 ';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Session2:
Mysql> update test set c = '000000' where B = '000000 ';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Conclusion: When an index is used (and I also tested whether the index is useless or the row lock), the row lock is used. Otherwise, the entire table is locked, it is convenient to have no row locks in Oracle.

MySQL InnoDB Storage engine lock mechanism Experiment

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

MySQL Innodb independent tablespace Configuration

This article permanently updates the link address: