3

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?

Avinash
  • 6,064
  • 15
  • 62
  • 95

3 Answers3

3

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)
dera
  • 31
  • 3
1

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.

rogal111
  • 5,874
  • 2
  • 27
  • 33
  • {process_id} would be the mysql database process ID? – Avinash Nov 24 '11 at 09:46
  • @Avinash No, it is rather the process id of the thread or the user session_id. A unique number by which you will know which thread locked the row, so you can later select for update right row(s). – rogal111 Nov 24 '11 at 09:55
0

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.

ravnur
  • 2,772
  • 19
  • 28