Preface
As we know,InnoDB is index organized table.InnoDB engine supports row-level lock base on indexes,if there're no indexes on a certain table the record locks will upgrade to "table-level"(not really table lock,just locks all the records in the table) locks.Furthe more,in RR transaction isolation mode,It's more complicated.'cause there're gap locks(together with record locks,we call them next key locks) to prevent phantom read between multiple tansactions.Let's do some test watch the locking conflicts.
Procedure
Crete a test table as below.
zlm@192.168.56.100: [zlm]>create table t1(
-> c1 int unsigned not null default '',
-> c2 int unsigned not null default '',
-> c3 int unsigned not null default '',
-> c4 int unsigned not null default '',
-> primary key(c1),
-> key(c2)
-> ) engine=innodb;
Query OK, rows affected (0.02 sec) zlm@192.168.56.100: [zlm]>insert into t1(c1,c2,c3,c4) values(,,,),(,,,),(,,,),(,,,),(,,,),(,,,),(,,,);
Query OK, rows affected (0.01 sec)
Records: Duplicates: Warnings: zlm@192.168.56.100: [zlm]>select * from t1;
+----+----+----+----+
| c1 | c2 | c3 | c4 |
+----+----+----+----+
| | | | |
| | | | |
| | | | |
| | | | |
| | | | |
| | | | |
| | | | |
+----+----+----+----+
rows in set (0.01 sec) zlm@192.168.56.100: [(none)]>select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ | //Make surej in RR transaction isolation level.
+-------------------------+
row in set (0.00 sec) zlm@192.168.56.100: [(none)]>show variables like 'innodb_status_output_locks';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_status_output_locks | ON |
+----------------------------+-------+
row in set (0.00 sec)
Test 1. session1 executes "select ... for update" and session2 executes "select ... lock in share mode".(conflict)
//Session1：
zlm@192.168.56.100: [zlm]>begin;select * from t1 where c1= for update;
Query OK, rows affected (0.00 sec) +----+----+----+----+
| c1 | c2 | c3 | c4 |
+----+----+----+----+
| | | | |
+----+----+----+----+
row in set (0.00 sec) //Session2:
monitor@192.168.56.100: [zlm]>begin;select * from t1 where c1= lock in share mode;
Query OK, rows affected (0.00 sec) ERROR (HY000): Lock wait timeout exceeded; try restarting transaction //Session2 requested a "S" record lock on the primary key column where c1=3 while session1 has holded the "X" record lock on the same position,so session2 was blocked util lock timeout.
Test 2. session1 executes "select ... for update" and session2 executes ordinary query.(compatible)
//Session1：
zlm@192.168.56.100: [zlm]>begin;select * from t1 where c1= for update;
Query OK, rows affected (0.00 sec) +----+----+----+----+
| c1 | c2 | c3 | c4 |
+----+----+----+----+
| | | | |
+----+----+----+----+
row in set (0.00 sec) //Session2:
monitor@192.168.56.100: [zlm]>select * from t1 where c1=;
+----+----+----+----+
| c1 | c2 | c3 | c4 |
+----+----+----+----+
| | | | |
+----+----+----+----+
row in set (0.00 sec) //Session1 didn't change this time and session2 request for non-lock consistent read.It read records from a consistent snapshop without locking.
Test 3. session1 executes "select ... lock in share mode" and session2 executes "select ... for update".(conflict)
//Session1:
zlm@192.168.56.100: [zlm]>begin;select * from t1 where c3= lock in share mode;
Query OK, rows affected (0.01 sec) Empty set (0.00 sec) //Session2:
monitor@192.168.56.100: [zlm]>begin;select * from t1 where c3= for update;
Query OK, rows affected (0.00 sec) ERROR (HY000): Lock wait timeout exceeded; try restarting transaction //Although there's no record satisfied with c3=7 but notice that there's no index on c3 column.Therefore,the session1 has holded a "S" record for all the records on column c1 in table t1.Then session2 asked for the "X" record lock for "c3=10"(even it does not exixt),it was blocked.
Test 4. session1 executes "select ... lock in share mode" and session2 executes "select ... for update".(conflict)
//Session1:
zlm@192.168.56.100: [zlm]>begin;select * from t1 where c3= lock in share mode;
Query OK, rows affected (0.00 sec) Empty set (0.00 sec) //Session2:
monitor@192.168.56.100: [zlm]>begin;select * from t1 where c1= for update;
Query OK, rows affected (0.00 sec) ERROR (HY000): Lock wait timeout exceeded; try restarting transaction //This is similar with "test 3".Session1 has holded a "S" record lock of all records on column c3.The record where c1=6 means c3=5,it's also in the range of all records.So session2 was blocked.
Test 5. session1 executes "select ... for update" and session2 executes "select ... for update".(conflict)
//Session1:
zlm@192.168.56.100: [zlm]>begin;select * from t1 where c2= and c3= for update;
Query OK, rows affected (0.00 sec) +----+----+----+----+
| c1 | c2 | c3 | c4 |
+----+----+----+----+
| | | | |
+----+----+----+----+
row in set (0.00 sec) //Session2:
monitor@192.168.56.100: [zlm]>begin;select * from t1 where c2= and c3= for update;
Query OK, rows affected (0.00 sec) ERROR (HY000): Lock wait timeout exceeded; try restarting transaction //Because of the secondary index key on column c2,it generated a "X" record lock and a gap lock(record + gap = next key lock).Although the gap lock between two sessions can be coexistent,but record locks do not.So session2 was blocked.
Test 6. session1 executes "select ... for update" and session2 executes "select ... for update".(compatible)
//Session1:
zlm@192.168.56.100: [zlm]>begin;select * from t1 where c2= and c3= for update;
Query OK, rows affected (0.00 sec) +----+----+----+----+
| c1 | c2 | c3 | c4 |
+----+----+----+----+
| | | | |
+----+----+----+----+
row in set (0.00 sec) //Session2:
monitor@192.168.56.100: [zlm]>begin;select * from t1 where c2= and c3= for update;
Query OK, rows affected (0.00 sec) Empty set (0.00 sec) //This time session2 was not blocked.They've requested a different "X" record lock individually even they still hold the gap lock.
Test 7. session1 executes "select ... for update" and session2 executes "select ... for update".(conflict)
//Session1:
zlm@192.168.56.100: [zlm]>begin;select * from t1 where c2= and c3= for update;
Query OK, rows affected (0.00 sec) +----+----+----+----+
| c1 | c2 | c3 | c4 |
+----+----+----+----+
| | | | |
+----+----+----+----+
row in set (0.00 sec) //Session2:
monitor@192.168.56.100: [zlm]>begin;select * from t1 where c1= and c3= for update;
Query OK, rows affected (0.00 sec) ERROR (HY000): Lock wait timeout exceeded; try restarting transaction //The query condition in session2 is c1=4.It means c2=2,this is similar with test 5(asked for the equal line).
Test 8. session1 executes "select ... for update" and session2 executes "select ... for update".(compatible)
//Session1:
zlm@192.168.56.100: [zlm]>begin;update t1 set c4= where c2>=;
Query OK, rows affected (0.00 sec) Query OK, rows affected (0.00 sec)
Rows matched: Changed: Warnings: //Session2:
monitor@192.168.56.100: [zlm]>begin;select * from t1 where c1= for update;
Query OK, rows affected (0.00 sec) Empty set (0.00 sec) //The records according to the query condition c2>=4 were c1=8 and c1=10.
//Even though there's a index key on c1 but it's a primary key which doesn't generate gap lock.So session2 's asking for "X" record lock of c1=7 was not blocked.
Summary
- We should pay more attention to innodb row-level locks.If there's no key on the relevant column,the locks will be escalated to "table-level"(all records will be locked) locks.
- In the RR transaction isolation level,Secondary index generates gap locks(LOCK_ORDINARY) to prevent phantom read while primary index and unique index do not.They only hold record locks(LOCK_REC_NOT_GAP).
- In the RC transaction isolation level,there're no gap locks.Therefore,it's concurrency is better than that in RR mode,but the consistency is poor as well.
- As for which transaction isolation level we should choose is depend on your purpose:for more consistency or for more concurrency.