Mysql 中select * from table where …... for update的用法

 由于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;

id	name
1	bingo

1 row in set (0.00 sec)

窗口二：

mysql> select * from t where id = 1;

id	name
1	bingo

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;

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

id	name
1	qweq
2	qw
3	vqw
4	vqws
5	vqs

由此得出结论，在没有此资料的情况下，即使你for update也是不锁的

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

id	name
1	qweq

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;

id	name
1	qweq
3	vqw
4	vqws
5	vqs

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;

id	name
3	vqw

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)中才能生效。