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