Research on MySQL INNODB storage engine exclusive lock and shared lock

1, Shared lock experiment

Session1

Build a table Lisa and insert data in Session1

Mysql> CREATE table Lisa (name char (ten), age int (5));

Mysql> INSERT into Lisa values (' Lisa ', ' 26 ');

Add a shared lock to the age=26 line

Mysql> set autocommit=0;

Mysql> SELECT * from the Lisa where age=26 lock in share mode;

Mysql> update Lisa set age= ' in ' WHERE name = ' Lisa ';

Update Successful! Such as:

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s1.51cto.com/wyfs02/m01/8b/e3/wkiom1hbhypqw3d_aabobg8_qt0740.png-wh_500x0-wm_3-wmp_4-s_2103707298.png "title = "Clipboard.png" alt= "Wkiom1hbhypqw3d_aabobg8_qt0740.png-wh_50"/>

Session2

Unable to update and query as follows:

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/8B/DF/wKioL1hbhanyGN7IAAAh6WHAXhQ023.png-wh_500x0-wm_3 -wmp_4-s_3369720097.png "title=" Clipboard.png "alt=" Wkiol1hbhanygn7iaaah6whaxhq023.png-wh_50 "/>

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

Summary: Sharing and exclusive locks in the InnoDB storage engine are row locks, and you must use where to specify the rows to lock.

The other session cannot be updated and queried after the shared lock is added.

2, exclusive lock test

Session2

Table Sun and insert data in Session1

Mysql> CREATE table Sun (name char (ten), age int (4));

Mysql> INSERT into sun values (' Lisa ', ' 26 ');

Add an exclusive lock to the age=26 line.

mysql> Set autocommit = 0;

Mysql> select * from Sun where age= ' + ' for update;

Mysql> update sun set name= ' Winter ' where age= ' 26 ';

Update Successful! Such as:

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s2.51cto.com/wyfs02/m02/8b/e3/wkiom1hbhcjamo89aabivr_1f_a125.png-wh_500x0-wm_3-wmp_4-s_3977621709.png "title = "Clipboard.png" alt= "Wkiom1hbhcjamo89aabivr_1f_a125.png-wh_50"/>

Session3

In the Session3 can no longer add to age=26 this row lock, also can not update age=26 this line, and the query to the data is not the latest update in Session2 data.

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s1.51cto.com/wyfs02/m00/8b/df/wkiol1hbhemydz41aabhxewlq-8208.png-wh_500x0-wm_3-wmp_4-s_237209320.png "title= "Clipboard.png" alt= "Wkiol1hbhemydz41aabhxewlq-8208.png-wh_50"/>

-----------------------------------------------------------------

Create a new table in Session2 Tab_no_index and insert the data

Mysql> CREATE TABLE Tab_no_index (ID int,name varchar (ten)) Engine=innodb;

mysql> INSERT INTO Tab_no_index values (1, ' 1 '), (2, ' 2 '), (3, ' 3 '), (4, ' 4 ');

Mysql> set autocommit=0;

Mysql> SELECT * from Tab_no_index where id=1;

Mysql> SELECT * from Tab_no_index where id=1 for update;

To id=1 this line with exclusive locks such as

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s4.51cto.com/wyfs02/m00/8b/e3/wkiom1hbhgtawgxhaabr8a8t8fy908.png-wh_500x0-wm_3-wmp_4-s_2257890618.png "title = "Clipboard.png" alt= "Wkiom1hbhgtawgxhaabr8a8t8fy908.png-wh_50"/>

Query the new table in Session1 Ab_no_index error!

Mysql> select * from Tab_no_index;
ERROR 1412 (HY000): Table definition has changed, please retry transaction

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/8B/E3/wKiom1hbhieRM-l7AABMGSs6O4A571.png-wh_500x0-wm_3 -wmp_4-s_1025416795.png "title=" Clipboard.png "alt=" Wkiom1hbhierm-l7aabmgss6o4a571.png-wh_50 "/>

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

What I did was to add a row of shared locks to the table Lisa in the Session1,

Why do I get an error when I check other forms???

How to resolve the error??

Workaround:

Mysql> commit;

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s3.51cto.com/wyfs02/m00/8b/e3/wkiom1hbi9txf4fhaaba1darxde506.png-wh_500x0-wm_3-wmp_4-s_245007506.png "title= "Clipboard.png" alt= "Wkiom1hbi9txf4fhaaba1darxde506.png-wh_50"/>

Cause of Error:

The transaction isolation level for the table I created is repeatable read (Repeatable-read),

Not read commit (read-committed), in other session queries need commit or rollback to find my updated data. That is, the nth select is the same as the value of the first select, and only a commit or rollback can see the changed value.

Is the operation that is done in the session2 of this article in the session that created the table.

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>650 "this.width=650;" src= "http ://s4.51cto.com/wyfs02/m01/8b/e3/wkiom1hbi-2g0uchaaawpps0x5o686.png-wh_500x0-wm_3-wmp_4-s_3239369734.png "title = "Clipboard.png" alt= "Wkiom1hbi-2g0uchaaawpps0x5o686.png-wh_50"/>

Research on MySQL INNODB storage engine exclusive lock and shared lock