CODEWITHSUNDEEP
mysqlsqlselecttransactionsmariadb
Serge Rogatch
Serge Rogatch

Reputation: 15080

LOCK IN SHARE MODE and FOR UPDATE vs nothing in MariaDB

This question compares SELECT...LOCK IN SHARE MODE and SELECT...FOR UPDATE. However, how are these 2 different from the bare SELECT inside a transaction? The database is MariaDB, but MySQL answer should be fine too if there are no differences. Most importantly, I need the info in the context of avoiding DB deadlocks.

Upvotes: 1

Views: 1395

Answers (1)

Rick James
Rick James

Reputation: 142453

Many deadlocks can be avoided; some cannot. There is no single technique to prevent all deadlocks.

FOR UPDATE seems to be more useful than LOCK IN SHARE MODE. (I would be interested to hear someone argue the contrary.)

The usual pattern seems to be:

BEGIN;
SELECT ... FOR UPDATE;  -- use the columns fetched; lock the row(s)
-- Note:  the locked rows may or may not actually be UPDATEd later
-- Note:  FOR UDPATE prevents other connections from grabbing the same row(s)
UPDATE/DELETE/... -- those rows.
COMMIT;

But... Nothing above prevents the classic deadlock scenario:

BEGIN;   -- connection 1
SELECT row 123 FOR UPDATE;
SELECT row 234 FOR UPDATE;
...

Versus

BEGIN;   -- connection 1
SELECT row 234 FOR UPDATE;
SELECT row 123 FOR UPDATE;  -- Note: locking order was swapped
...

Failing to do some form of SELECT ...LOCK/UPDATE can easily lead to a "race condition", wherein two connections are acting on the same row(s) in inconsistent ways.

Another note: If you "lock" the same rows in the same order, then one connection may stall until the other lets go. (cf innodb_lock_wait_timeout, which defaults to 50 seconds [an excessive amount of time, in my opinion])

(All of my discussion should apply to all versions of InnoDB/XtraDB in either MySQL or MariaDB.)

Upvotes: 2

Related Questions