0

I am using SELECT...FOR UPDATE queries to lock a table. Unfortunately, I have a situation where I need to lock two different sets of rows in the same table, like so:

SELECT * FROM mytable WHERE attribute1 = 'something' FOR UPDATE
SELECT * FROM mytable WHERE attribute2 = 'somethingelse' FOR UPDATE
UPDATE mytable SET  attribute2 = 'somethingnew' WHERE somethingelse

I need to lock both sets of rows. What I'm doing is, making sure none of the objects in the table are in particular state, and then finding other objects which can be put into that state and putting them there. The goal is to satisfy certain constraints which are too complex to be encoded into mysql.

So... the question is, what happens when I SELECT...FOR UPDATE twice from the same table inside the same transaction? Does the first lock get released? I've seen evidence that this is the case, but I can't quite figure out how to confirm.

Igor Serebryany
  • 3,307
  • 3
  • 29
  • 41

2 Answers2

1

Locks are only release at commit or rollback. On the other hand, a single transaction never blocks for itself. So the above should be fine, as long as only one thread is doing it.

If more than one thread is running the above, I fear you can deadlock. If you want that, you'll need a single SQL statement that locks both sets at once, as e.g. Jaydee's answer above.

Esben Mose Hansen
  • 560
  • 3
  • 9
0

How about something like

SELECT * FROM mytable WHERE attribute1 = 'something' 
    or attribute2 = 'somethingelse' FOR UPDATE

UPDATE mytable SET  attribute2 = 'somethingnew' WHERE somethingelse

I don't know the answer directly to your question, but the MySQL manual says locks a released when the transaction completes (Commited or Rolled back) which implies that a second select does not release the locks. In your case you don't actually need two selects.

Jaydee
  • 4,138
  • 1
  • 19
  • 20