情况1:
session 1:
localhost.test>create table g(a int,b int ,primary key(a,b)) engine = innodb;
Query OK, 0 rows affected (0.03 sec)
localhost.test>select * from g;
+----+---+
| a | b |
+----+---+
| 1 | 1 |
| 3 | 1 |
| 5 | 3 |
| 7 | 6 |
| 10 | 8 |
+----+---+
5 rows in set (0.00 sec)
localhost.test>begin;
Query OK, 0 rows affected (0.00 sec)
localhost.test>select * from g where a = 5 lock in share mode;
+---+---+
| a | b |
+---+---+
| 5 | 3 |
+---+---+
1 row in set (0.00 sec)
session2:
localhost.test>begin;
Query OK, 0 rows affected (0.00 sec)
localhost.test>insert into g select 7,7;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
localhost.test>insert into g select 3,2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
localhost.test>
锁住了前面没锁后面!
情况二:
当查询的索引含有唯一属性时,InnoDB存储引擎会对Next-Key Lock进行优化,将其降级为Record Lock:
session1:
localhost.test>begin;
Query OK, 0 rows affected (0.00 sec)
localhost.test>select * from t where a = 5 for update;
+---+
| a |
+---+
| 5 |
+---+
1 row in set (0.00 sec)
localhost.test>show create table t;
+-------+-----------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------+
session2:
localhost.test>begin;
Query OK, 0 rows affected (0.00 sec)
localhost.test>insert into t select 4;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
localhost.test>commit;
Query OK, 0 rows affected (0.00 sec)
成功,不需要等待!
情况三:
session1:
localhost.test>show create table z;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| z | CREATE TABLE `z` (
`a` int(11) NOT NULL DEFAULT '0',
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
localhost.test>begin;
Query OK, 0 rows affected (0.00 sec)
localhost.test>select * from z where b=3 for update;
+---+------+
| a | b |
+---+------+
| 5 | 3 |
+---+------+
1 row in set (0.00 sec)
session2:
localhost.test>begin;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3991
Current database: test
Query OK, 0 rows affected (0.00 sec)
localhost.test>select * from z where a = 5 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
localhost.test>insert into z select 4,2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
localhost.test>insert into z select 6,5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
localhost.test>insert into z select 6,6;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
localhost.test>insert into z select 6,7;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
localhost.test>insert into z select 4,1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
session 1:
localhost.test>create table g(a int,b int ,primary key(a,b)) engine = innodb;
Query OK, 0 rows affected (0.03 sec)
localhost.test>select * from g;
+----+---+
| a | b |
+----+---+
| 1 | 1 |
| 3 | 1 |
| 5 | 3 |
| 7 | 6 |
| 10 | 8 |
+----+---+
5 rows in set (0.00 sec)
localhost.test>begin;
Query OK, 0 rows affected (0.00 sec)
localhost.test>select * from g where a = 5 lock in share mode;
+---+---+
| a | b |
+---+---+
| 5 | 3 |
+---+---+
1 row in set (0.00 sec)
session2:
localhost.test>begin;
Query OK, 0 rows affected (0.00 sec)
localhost.test>insert into g select 7,7;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
localhost.test>insert into g select 3,2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
localhost.test>
锁住了前面没锁后面!
情况二:
当查询的索引含有唯一属性时,InnoDB存储引擎会对Next-Key Lock进行优化,将其降级为Record Lock:
session1:
localhost.test>begin;
Query OK, 0 rows affected (0.00 sec)
localhost.test>select * from t where a = 5 for update;
+---+
| a |
+---+
| 5 |
+---+
1 row in set (0.00 sec)
localhost.test>show create table t;
+-------+-----------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------+
localhost.test>begin;
Query OK, 0 rows affected (0.00 sec)
localhost.test>insert into t select 4;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
localhost.test>commit;
Query OK, 0 rows affected (0.00 sec)
成功,不需要等待!
情况三:
session1:
localhost.test>show create table z;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| z | CREATE TABLE `z` (
`a` int(11) NOT NULL DEFAULT '0',
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
localhost.test>select * from z where b=3 for update;
+---+------+
| a | b |
+---+------+
| 5 | 3 |
+---+------+
1 row in set (0.00 sec)
localhost.test>begin;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3991
Current database: test
Query OK, 0 rows affected (0.00 sec)
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
localhost.test>insert into z select 4,2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
localhost.test>insert into z select 6,5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
localhost.test>insert into z select 6,6;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
localhost.test>insert into z select 6,7;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29313086/viewspace-1966785/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29313086/viewspace-1966785/