1

Is it possible to get an explicit, independent lock on a given row with MySQL?

What I want is:

  • Read a row, and acquire a lock on it; if the row is already locked, wait for the lock to be released
  • Perform some processing
  • Write the row, and release the lock

I thought that SELECT FOR UPDATE would be my solution, but because it requires an explicit transaction, I can't do the following:

  1. Read & lock row 1
  2. Read & lock row 2
  3. Do something...
  4. Write & release row 2
  5. Do something...
  6. Write & release row 1

(or invert steps 4. and 6.)

Basically, I want to be able to explicitly acquire & release a lock on any row at any time, without having to wait to release all the locks at the same time using COMMIT. Hence the idea to do it without a transaction.

Is that achievable with MySQL?

BenMorel
  • 34,448
  • 50
  • 182
  • 322

1 Answers1

0

Use two transactions (across two database connections); or else, use triggers to make your records read-only.

Community
  • 1
  • 1
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • I'm trying to avoid using as many connections as there are locked records, and triggers unfortunately do not provide a failover in case a process dies without unlocking a record! – BenMorel Jul 12 '13 at 19:37
  • It's funny as it seems like an easy thing to do when using a filesystem (lock any file, unlock at any time, and as a failover locks are released when the file is closed), but it looks quite complicated to replicate that with a database! – BenMorel Jul 12 '13 at 19:39
  • @Benjamin: *normally* one tries to keep transactions as short as possible, so the time between steps 4 and 6 is so immaterial that this sort of detail is unimportant. What exactly happens in step 5 that takes so long? Is it really necessary to release the first lock and yet hold the second for the entire time? – eggyal Jul 12 '13 at 19:48
  • Actually I wouldn't really have control over when the transaction(s) would be committed: this is to be used as a session backend storage, where I need to be able to provide selective locking only on the parts of the session used in the current controller (as opposed to locking the whole session). Ideally, every session "key" can be loaded & locked at any time, and should be released as soon as possible; but the consumer of the session shouldn't be aware of what's happening behind the scenes. The database is just one of the backend storages available: with a file backend, everything is easier. – BenMorel Jul 12 '13 at 21:29