MYSQL锁

情况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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29313086/viewspace-1966785/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29313086/viewspace-1966785/