MySQL Experimental demonstration InnoDB table-level lock and row-level lock

InnoDB a row lock is a table that has no index locked in the case of an index.

Table lock Demo (no index)

Session1:

Mysql> set autocommit=0;

Mysql> select * from Innodb_test;
+------+-------------+
| ID | name |
+------+-------------+
| 1 | Woshiceshi |
| 2 | Woshiceshi2 |
| 3 | Woshiceshi3 |
+------+-------------+

Mysql> SELECT * from innodb_test where id = 2 for update;
+------+------------+
| ID | name |
+------+------------+
| 2 | Woshiceshi2 |
+------+------------+

Session2:

mysql> Update innodb_test set name= ' sjis ' where id = 1;
In the waiting state ....

Then back to Session1 commit, session2 out the result (locked for 8 seconds, after 8 seconds or so to session1 submit).

mysql> Update innodb_test set name= ' sjis ' where id = 1;
Query OK, 1 row affected (8.11 sec)
Rows matched:1 changed:1 warnings:0

The experimental result: my for update operation in Session1 looks like locking only the row with ID 2 actually locks the full table, so that the subsequent session2 of the row update with ID 1 needs to wait for the Session1 lock to be released.

Row lock Demo (indexed as ID)

Session1:
Mysql> ALTER TABLE Innodb_test Add index idx_id (ID);
Query OK, 4 rows affected (0.01 sec)
Records:4 duplicates:0 warnings:0

Mysql> SELECT * from innodb_test where id = 2 for update;
+------+------------+
| ID | name |
+------+------------+
| 2 | Woshiceshi2 |
+------+------------+

Session2:

mysql> Update innodb_test set name= ' Wohaishiceshi ' where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched:1 changed:1 warnings:0
Mysql> SELECT * from innodb_test where id = 1;
+------+---------------+
| ID | name |
+------+---------------+
| 1 | Wohaishiceshi |
+------+---------------+
1 row in Set (0.00 sec)

Experimental results: This time the lock is a locked row, so there is no locked line (the row with ID not 2) can be update.

MySQL Experimental demonstration InnoDB table-level lock and row-level lock