由于InnoDB預(yù)設(shè)是Row-Level Lock，所以只有「明確」的指定主鍵，MySQL才會(huì)執(zhí)行Row lock (只鎖住被選取的資料例) ，否則MySQL將會(huì)執(zhí)行Table Lock (將整個(gè)資料表單給鎖住)。

舉個(gè)例子: 假設(shè)有個(gè)表單t，里面有id跟name二個(gè)欄位，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;

1 row in set (0.00 sec)

窗口二：

mysql> select * from t where id = 1;

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

由此可見(jiàn)，當(dāng)明確指定主鍵，并且有此資料時(shí)，鎖的是where后面的記錄，即這里的id= 1； 接下來(lái)來(lái)看下沒(méi)有此資料的情況下會(huì)不會(huì)被鎖。

例2: (明確指定主鍵，若查無(wú)此筆資料，無(wú)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;

+----+------+

由此得出結(jié)論，在沒(méi)有此資料的情況下，即使你for update也是不鎖的

例2: (無(wú)主鍵，table lock)
窗口1：
mysql> select * from t where name ='qweq' for update;

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

由此可見(jiàn)，沒(méi)有明確主鍵的情況下鎖全表；

例3: (主鍵不明確，table lock)

SELECT * FROM products WHERE id<>'3' FOR UPDATE;
窗口1：

mysql> select * from t where id <> 2 for update;

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;

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

鎖全表