How to handle "select for update" job sequence in mysql innodb table

Question

I have a constant stream of "select ... for update" queries in the inndob table.

The basic condition is that it selects all fields where 'column1' is <less than 100.

In the background, constant inserts occur, which can include inserts where "column1" is <less than 100, but this is not a problem. If the first choice for updating skips it because it occurs during the execution of this query or while the array of results is retrieved, the next one will catch it, and I am glad to mark the first query as missing because it was 'too late.'

If I have 10 of these “select for upgrade” queries because of blocking the inndob field, should I manage their queue myself or allow sorting of the database? I guess the right way to handle this is to queue the queries yourself?

So when the script reaches

$sql = "SELECT * FROM ... FOR UDPATE" 

Check the queue array (?) First, if the queue array is not empty, put this script call in the queue array at the end, and then check the queue array every few milliseconds until it reaches number 1 in the queue

I think of the correct lines here ... It is important that I understand this now, and not return to it later

Edit: is there anything I can add to increase the likelihood of an answer :)

Answer 1

InnoDB should handle the queue for you. If a transaction is executed with locks in the corresponding rows, then the second transaction tries to get the same locks with another SELECT FOR UPDATE statement, after which the second statement will wait until the first transaction is completed.

You can check it yourself:

• Open two bash shell windows and start the mysql client in each window.
• Run START TRANSACTION in each window.
• Execute a SELECT ... FOR UPDATE in the first window.
• Then do the same in the second window with the WHERE clause, which should overlap the same lines.

• In the first window, run SHOW ENGINE INNODB STATUS and view the transactions and their locks. You should see that the output is something like the following:

   LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 3B17, ACTIVE 4 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 2, OS thread handle 0x7ff27ae2d700, query id 28 192.168.56.1 root Sending data select * from foo where id < 100 for update ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 266 page no 3 n bits 72 index `PRIMARY` of table `test`.`foo` trx id 3B17 lock_mode X waiting ------------------ TABLE LOCK table `test`.`foo` trx id 3B17 lock mode IX RECORD LOCKS space id 266 page no 3 n bits 72 index `PRIMARY` of table `test`.`foo` trx id 3B17 lock_mode X waiting ---TRANSACTION 3B16, ACTIVE 70 sec 2 lock struct(s), heap size 376, 2 row lock(s) MySQL thread id 1, OS thread handle 0x7ff27ae6e700, query id 29 192.168.56.1 root show engine innodb status TABLE LOCK table `test`.`foo` trx id 3B16 lock mode IX RECORD LOCKS space id 266 page no 3 n bits 72 index `PRIMARY` of table `test`.`foo` trx id 3B16 lock_mode X 

Note that transaction 3B16 contains locks, and transaction 3B17 is waiting for locks.

Transaction 3B16 shows that it is currently running showon innodb status, but it still holds the locks that it acquired in the previous SELECT ... FOR UPDATE. Although this statement is completed, the transaction is not executed, and locks are released when the transaction ends.

If transaction 3B17 waits more than lock_wait_timeout seconds, and the first transaction has not yet been completed or rolled back, then the wait statement resets and gives this error:

 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction