UPDATE...WHERE...ORDER BY...LIMIT語句，updatelimit

UPDATE...WHERE...ORDER BY...LIMIT語句，updatelimit

在MySQL中盡量少使用UPDATE ...WHERE ...ORDER BY ...LIMIT語句，原因是MySQL會對where條件匹配的所有記錄加上X鎖，如果多個線程同事執行這條語句，就會有非常大的機率發生死結，而且MySQL伺服器不能自動的去解除這種死結。

下面的例子證實了這一點，先建立一張record表，再插入幾條記錄

mysql> create table record(    -> id int(11) primary key,    -> name varchar(20) default '',    -> amount int(11) default 0,    -> date datetime default null    -> )engine=innodb;Query OK, 0 rows affected (0.13 sec)mysql> insert into record values    -> (1,'tom',100,'2014-12-12'),    -> (2,'Jack','200','2014-11-11'),    -> (3,'david','500','2013-11-11'),    -> (4,'tom','400','2011-11-11'),    -> (5,'tom','600','2015-01-11'),    -> (6,'tom','1000','2010-01-11');Query OK, 6 rows affected (0.01 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> select * from record;+----+-------+--------+---------------------+| id | name  | amount | date                |+----+-------+--------+---------------------+|  1 | tom   |    100 | 2014-12-12 00:00:00 ||  2 | Jack  |    200 | 2014-11-11 00:00:00 ||  3 | david |    500 | 2013-11-11 00:00:00 ||  4 | tom   |    400 | 2011-11-11 00:00:00 ||  5 | tom   |    600 | 2015-01-11 00:00:00 ||  6 | tom   |   1000 | 2010-01-11 00:00:00 |+----+-------+--------+---------------------+6 rows in set (0.00 sec)

下面開啟兩個session，同時對這張表操作

在Seesion A中進行如下操作，將tom的最近消費的記錄金額免單，實際只會使用到一條記錄，即id=5的記錄

mysql> set autocommit = 0;Query OK, 0 rows affected (0.00 sec)mysql> update record set amount = 0 where name='tom' order by date desc limit 1;Query OK, 1 row affected, 1 warning (0.03 sec)Rows matched: 1  Changed: 1  Warnings: 1

這條語句這時不用commit，切換到Seesion B中進行如下操作，將Tom的2011-01-01之前的記錄全部免單，會使用到id=6的記錄

mysql> update record set amount = 0 where date < '2011-01-01';

這時可以發現這條語句的執行阻塞了，因為在session A中，把全部name='tom'的記錄都加上了獨佔鎖定，在session A的事務沒有commit之前，任何對這些記錄的修改操作只能等待，如果多個事務同時操作，很可能就造成死結。

上述情況，只需要在session A中執行commit操作，B的阻塞就會解除。

mysql> commit;Query OK, 0 rows affected (0.00 sec)mysql> select * from record;+----+-------+--------+---------------------+| id | name  | amount | date                |+----+-------+--------+---------------------+|  1 | tom   |    100 | 2014-12-12 00:00:00 ||  2 | Jack  |    200 | 2014-11-11 00:00:00 ||  3 | david |    500 | 2013-11-11 00:00:00 ||  4 | tom   |    400 | 2011-11-11 00:00:00 ||  5 | tom   |      0 | 2015-01-11 00:00:00 ||  6 | tom   |      0 | 2010-01-11 00:00:00 |+----+-------+--------+---------------------+6 rows in set (0.00 sec)