Table lock, Row lock difference

In the invocation of the stored procedure, the table lock is involved, the concept of row lock: The so-called difference: when indexed is a row lock, when there is no index is the table.

InnoDB row locks are indexed, and tables with no indexes are locked across the table.

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 wait state ....

Then back to Session1 commit, Session2 came out of the result (locked for 8 seconds, after 8 seconds or so to Session1 submitted).

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 result: My for update operation in Session1 seemed to lock only the rows with ID 2 in fact locking the whole table so that the subsequent session2 of row update with ID 1 had to wait for the release of the Session1 lock.

Row lock Demo (index is 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)

Experiment results: This time the lock is a locked row, so there are no locked rows (rows with an ID of 2) can be update ...