How to Test Select for Update in MySQL

Related searches

I am performing SELECT ... FOR UPDATE or row level locking with InnoDB tables.

My intention is to only one request can read the same row. So if two users make request for the same data as the same time. Only one of them get data, who fires the query first.

But How can i test that locking is placed or not. as I am testing it by retrieving the same data at same time and both users getting the data.

Note: My tables are InnoDB, My query executes in transaction, my query as below:

SELECT * FROM table_name WHERE cond FOR UPDATE;

Any other thing I have to check for this to make work?


open 2 mysql client session.

on session 1:

mysql> start transaction;

mysql> SELECT * FROM table_name WHERE cond FOR UPDATE;

... (result here) ...

1 row in set (0.00 sec)

on session 2:

mysql> start transaction;

mysql> SELECT * FROM table_name WHERE cond FOR UPDATE;

... (no result yet, will wait for the lock to be released) ...

back to session 1, to update selected record (and release the lock):

mysql> UPDATE table_name SET something WHERE cond;

mysql> commit;

back to session 2:

1) either showing lock timeout error

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

2) or showing result

... (result here) ...

1 row in set (0.00 sec)

3) or showing no result (because corresponding record has been modified, so specified condition was not met)

Empty set (0.00 sec)

How to Test "Select for Update" in MySQL — Percona Community , I am performing SELECT FOR UPDATE</pre> or row level locking with InnoDB tables. The WHERE clause specifies the row with employee number 1056 will be updated. The SET clause sets the value of the email column to the new email. Third, execute the SELECT statement again to verify the change: SELECT firstname, lastname, email FROM employees WHERE employeeNumber = 1056;


You can use own lock mechanizm with lock_by column.

UPDATE table_name SET locked_by=#{proccess_id} WHERE cond and locked_by IS NULL

Now in your program you will get count of affected rows:

if(affected_rows==0)
   return 'rows locked'
else
   //do your staff with locked_by=#{process_id} rows

With this mechanism you can control locked rows and locking processes. You can also add in UPDATE statement locked_at=NOW() to get more info about locked row.

Don't forget to add some index on locked_by column.

MySQL 8.0 Reference Manual :: 15.7.2.4 Locking Reads, If you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. MySQL Forums Forum List hard to test select for update scenario. 1947. Andrew Nuss. February 13, 2013 03:03PM Re: hard to test select for update scenario. 872


Here is MySQL docs about working with locks.

Before update you can put lock, releasing it after. In another transaction you can check lock using it unique name. Strategy for naming you can choose yourself.

MySQL 5.6 Reference Manual :: 14.7.2.4 Locking Reads, If you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. a) select t.mykey from LockTable t where t.key in (:sortedkeylist) for update b) update t.mydummycount set t.mydummycount = t.mydummycount + 1 where t.mykey in (:sortedkeylist) c) same as a, but iterating the sorted list in order in the client and selecting one key at a time


SELECT FOR UPDATE, Have you ever experienced the unexpected updates to the rows you have just requested in MySQL? In this article, we will tell you how to secure your requests� We can create a stored procedure with IN operator to update values in a MySQL table. To make it understand we are taking an example of a table named ‘student_info’ having the following data −


MySQL SELECT FOR UPDATE statement - Querychat, Description: If a SELECT FOR UPDATE is executed at the same time as FLUSH TABLES WITH READ LOCK (for example via mysqldump using --master-data), the two can lock endlessly, the SELECT FOR UPDATE "Waiting for release of readlock" and the FLUSH.. "Flushing tables", waiting until either is killed, SHOW TABLE STATUS is run or ANALYZE TABLE is run.


To test whether a row exists in a MySQL table or not, use exists condition. The exists condition can be used with subquery. It returns true when row exists in the table, otherwise false is returned.