由于InnoDB预设是Row-Level Lock,所以只有「明确」的指定主键,MySQL才会执行Row lock (只锁住被选取的资料例) ,否则MySQL将会执行Table Lock (将整个资料表单给锁住)。
举个例子: 假设有个表单t,里面有id跟name二个栏位,id是主键。 CREATE TABLE t ( id int(11) NOT NULL DEFAULT '0', name varchar(255) DEFAULT NULL, PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
例1: (明确指定主键,并且有此笔资料,row lock) 窗口一:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id = 1 for update; idname
1bingo
1 row in set (0.00 sec)
窗口二:
mysql> select * from t where id = 1; idname
1bingo
1 row in set (0.01 sec)
mysql> update t set name = 'xxm' where id = 2;
Query OK, 1 row affected
(0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'icey' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
由此可见,当明确指定主键,并且有此资料时,锁的是where后面的记录,即这里的id= 1; 接下来来看下没有此资料的情况下会不会被锁。
例2: (明确指定主键,若查无此笔资料,无lock)
窗口1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id = 11 for update;
Empty set (0.00 sec)
窗口2:
mysql> update t set name = 'qweq' where id = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'qw' where id = 2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'vqw' where id = 3;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'vqws' where id = 4;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'vqs' where id = 5;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set name = 'vqs' where id = 11;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> select * from t;
+----+------+ idname
1qweq
2qw
3vqw
4vqws
5vqs
由此得出结论,在没有此资料的情况下,即使你for update也是不锁的
例2: (无主键,table lock)
窗口1:
mysql> select * from t where name ='qweq' for update; idname
1qweq
1 row in set (0.00 sec)
窗口2:
mysql> update t set name = 'vqs' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set name = 'vqs' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
由此可见,没有明确主键的情况下锁全表;
例3: (主键不明确,table lock)
SELECT * FROM products WHERE id<>'3' FOR UPDATE;
窗口1:
mysql> select * from t where id <> 2 for update; idname
1qweq
3vqw
4vqws
5vqs
4 rows in set (0.00 sec)
窗口2:
mysql> update t set name = 'vqs' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set name = 'vqs' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
在主键不明确的情况下,锁全表
例4: (主键不明确,table lock)
窗口1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where id like 3 for update; idname
3vqw
1 row in set (0.00 sec)
窗口2:
mysql> update t set name = 'vqs' where id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update t set name = 'vqs' where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
锁全表 注1: FOR UPDATE仅适用于InnoDB,且必须在交易区块(BEGIN/COMMIT)中才能生效。