1

Following up on https://stackoverflow.com/a/16553083/14731...

I understand that it is important to maintain a consisting locking order for tables in order to reduce the frequency of deadlocks, and that this affects both UPDATE and SELECT statements [1]. But, does the same hold true for read-only rows?

If a row is populated once at initialization time and no one modifies it ever again, does it really matter what order we access it?

Given two transactions: T1, T2 and two read-only rows R1, R2

T1 reads R1, then R2 T2 reads R2, then R1

Can the transactions deadlock, even if I use SERIALIZABLE transaction isolation?

[1] If transaction isolation is REPEATABLE_READ, T1 SELECTs R1, R2 while T2 UPDATEs R2, R1 a deadlock may occur.

CLARIFICATION: This question is not RDBMS-specific. I am under the impression that no implementation can deadlock on read-only rows. If you have a counter-example (for a concrete vendor), please post an answer demonstrating as much and I will accept it. Alternatively, post a list of all concrete implementations that you can prove will not deadlock (and the most complete list will get accepted).

Community
  • 1
  • 1
Gili
  • 86,244
  • 97
  • 390
  • 689
  • 1
    What brand of RDBMS are you using? Please tag your question. Each vendor may have implementation details that affect the answer. – Bill Karwin Sep 05 '14 at 00:29
  • @BillKarwin, I was/am under the impression that the answer will span all implementations. If you have a concrete example of a statement that deadlocks on one vendor but not on another, please post an answer demonstrating as much and I will accept it. – Gili Sep 05 '14 at 05:10
  • 1
    Well, MySQL's InnoDB engine for example does not lock on read, so deadlock is not going to happen except between multiple writers. That is probably the case for any implementation that uses MVCC architecture. – Bill Karwin Sep 05 '14 at 05:11
  • @BillKarwin, to clarify: the question is asking "Are you aware of any implementations where deadlocks *do* occur? If so, please provide a concrete example." – Gili Sep 05 '14 at 05:14
  • I don't know of any, but I'm not an expert in locking implementation of every RDBMS. – Bill Karwin Sep 05 '14 at 15:20

1 Answers1

0

This question is impossible to answer for all possible RDBMS's because the locking strategy is an implementation detail. That said, useful RDBMS's will share some common characteristics:

For SELECT statements without hints applied (FOR UPDATE, WITH (UPDLOCK), ...) any reasonable RDBMS will not take write-locks. It might take read-locks. Indeed, at least SQL Server does so for SERIALIZABLE except on Hekaton tables.

Read-locks never conflict. No deadlock is possible if only reads are being executed.

Read-only rows can however cause deadlocks even if they are never written to. In SQL Server,

UPDATE T SET SomeCol = 1 WHERE ID = 10 AND SomeCol = 0

will take an U-lock on the row with ID 10. If SomeCol is not 0 the lock will be released immediately and nothing will be written. But the U-lock is a lock type that can potentially conflict and lead to a deadlock. Had the row with ID 10 not been present no deadlock would have been possible.

usr
  • 168,620
  • 35
  • 240
  • 369
  • Where is the read-only row in the example `UPDATE T SET SomeCol = 1 WHERE ID = 10 AND SomeCol = 0`? I assume the row that matches the `WHERE` clause is not read-only because you're updating it. – Gili Sep 06 '14 at 21:22
  • @Gili because the WHERE does not match it is never being written to. If there was a row with `ID = 11` you wouldn't say that this row is being updated, either. In this regard, the row with `ID = 11` and the row with `ID = 10 AND FALSE` are equivalent. Not being updated. – usr Sep 06 '14 at 23:05
  • So you're saying non-matching rows will temporarily get write-locked but the lock will be removed as soon as the database notice they are not a match. Correct? I don't think such behavior could trigger a deadlock because typical lock timeouts are counted in seconds ([50 seconds for MySQL](http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout) and [1 second for Postgres](http://www.postgresql.org/docs/9.1/static/runtime-config-locks.html)) whereas locks are typically acquired and released in under 5ms. – Gili Sep 08 '14 at 22:33
  • Correct.; You can't use wall-clock based timing to make statements about what is possible and what not. All modern OS'es can introduce arbitrary delays at any time. – usr Sep 09 '14 at 15:43
  • Fair enough. Thanks for clarifying. – Gili Sep 10 '14 at 17:16