UPDATE... WHERE... ORDER & #160; BY... LIMIT statement, updatelimit

UPDATE... WHERE... order by... LIMIT statement, updatelimit

Use UPDATE as little as possible in MySQL... WHERE... order... the LIMIT statement is because MySQL adds an X lock to all records that match the where condition. If multiple threads execute this statement, a deadlock may occur, in addition, the MySQL server cannot automatically remove the deadlock.

The following example demonstrates how to create a record table and insert several records.

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)

Start two sessions and operate on this table at the same time.

In Seesion A, perform the following operations to exempt tom from the amount of Recently consumed records. In fact, only one record is used, that is, the record with 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

This statement does not need to use commit. Switch to Seesion B and perform the following operations to free all records earlier than Tom. Records with id = 6 will be used.

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

At this time, we can find that the execution of this statement is blocked, because in session A, the exclusive lock is applied to all records with name = 'Tom '. Before the transaction of session A is not commit, any modification operation on these records can only wait. If multiple transactions operate simultaneously, it is likely to cause a deadlock.

In the above case, you only need to execute the commit operation in session A, and B's blocking will be removed.

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)