CODEWITHSUNDEEP
mysqllockinginnodb
Askerman
Askerman

Reputation: 847

Locking a row in a MYSQL table during a transaction

I have a batch of queries like this:

START TRANSACTION
SELECT amount FROM piggybank WHERE id = 2
UPDATE piggybank SET amount = amount + 5 WHERE id = 1
COMMIT

I need to make sure that no one else can read or write the rows with ID 1 and 2, until the transaction ends. (Locking the whole table would be fine too) The problem is, that when I read the MySQL manual, it said that starting a transaction will clear any locks made before starting it and in turn, locking will commit any outgoing transaction.

But I need to both lock the rows (or table) and use the transactions at the same time as well.

Upvotes: 5

Views: 2389

Answers (1)

Mureinik
Mureinik

Reputation: 311308

using a select ... for update query inside the transaction should give you the semantics you want - other updates will be locked, and other sessions attempting to take this same lock will block until you commit:

START TRANSACTION
SELECT * FROM piggybank WHERE id IN (1, 2) FOR UPDATE;
SELECT amount FROM piggybank WHERE id = 2;
UPDATE piggybank SET amount = amount + 5 WHERE id = 1;
COMMIT

Upvotes: 5

Related Questions