-1

I have no knowledge of locking whatsoever. I have been looking through some MySQL documentation and can't fully understand how this whole process goes about. What I need, is for the following events in my script to happen:

step 1) table user gets locked
step 2) my script selects two rows from table user
step 3) my script makes an update to table user
step 4) table user gets unlocked because the script is done

How do I go about this exactly? And what happens when another user runs this same script while the table is locked? Is there a way for the script to know when to proceed (when the table becomes unlocked?). I have looked into start transaction and select for update but the documentation is very unclear. Any help is appreciated. And yes, the table is innodb.

user987048
  • 49
  • 7
  • Did you read about optimistic and pessimistic locking ? Example link from SO http://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking – Slawek Mar 02 '12 at 22:58

2 Answers2

0

Eliminate step 2 by performing your select query as part of your update call. Then MySQL takes care of the rest. Only one write query can run at the same time, others will be queued behind.

Julien
  • 212
  • 1
  • 18
  • 53
  • Can you please explain this a little more thoroughly? I forgot to mention that the script makes new data from step 2 to be updated into the user table during step 3. – user987048 Mar 02 '12 at 23:17
0

I believe what you are look for is the SELECT ... FOR UPDATE syntax available for InnoDB tables. This will lock only the records you want to update. You do need to wrap it in a transaction. http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

For example, run your queries like this:

START TRANSACTION
SELECT ... FOR UPDATE
UPDATE ...
COMMIT
Brent Baisley
  • 12,641
  • 2
  • 26
  • 39