UPDATE...WHERE...ORDER BY...LIMIT语句

在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: 0

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   |    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)