Hacker News Reader

Comment by slaymaker1907 | Hacker News Reader

slaymaker1907·

It's quite dangerous IMO since you run the risk of deadlock even in unexpected cases. There are times this makes sense, but most applications won't need it. Those cases are mainly where you have some long lived transactions that need to do a bunch of writes and you can guarantee they won't conflict since they are touching different tables.

View on HN

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.

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?

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.

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.

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

What's dangerous about deadlock? Sqlite doesn't throw error if it encounters deadlock?

Reply on news.ycombinator.com