Principle test of MySQL InnoDB Engine lock

Source: Internet
Author: User

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:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.