[RMarcus]

My reading is that no deadlocks should be possible since there is only one lock (pages are "locked" optimistically, meaning that the tx is aborted if the page has changed). "Live locks" are possible, where two repeatedly-reissued transactions cause each other to abort forever.

[anyfoo]

Above, scottlamb came to the conclusion that live lock isn't possible after all, because one transaction will always have made progress. Intuitively, that makes sense, but intuition alone is always dangerous with concurrency. Which is it?

[slaymaker1907]

After reading the post a bit more carefully, I think livelock is impossible since transactions are only aborted if one or more read pages have been modified since they were read. That implies forward progress must be made another transaction in order for a transaction to be aborted.

However, unless they do some very careful accounting, this sounds equivalent to snapshot isolation which has anomalies not found with serializable isolation, though the chance of this happening is reduced by using page level locking.

[jgraettinger1]

Right, unless they’re marking non-leaf pages as “read” — which would imply marking the root — it would have to be snapshot, right?

Otherwise you can’t properly abort the txn because a “select x where y” that was previously empty, no longer is.

[RMarcus]

Ah, I missed that this requires WAL mode -- indeed, if WAL is used, one transaction should always be able to make progress.