2 ms·
There's a typo, clearly the second transaction is removing C and not D as stated. > The first transaction modifies fields in records A and C, while the second
by magicalhippo 2y ago
There's a typo, clearly the second transaction is removing C and not D as stated.
> The first transaction modifies fields in records A and C, while the second transaction modifies fields in records B and D.
> Under Snapshot Isolation both transactions will commit, which means that the doubly linked list will be left in an inconsistent state
That's not how the DB we use works[1]. Both transactions will run, but as soon as either touches a row the other has already modified it will raise an "Update conflict on snapshot transaction" error. If the first transaction races the second, the second will be blocked on the row's write lock until the first is committed, and then raise the same error.
And yes, I verified this just now, and I verified the connections are running snapshot isolation.
So does SQLAnywhere not implement the standard way?
[1]: https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.sqlanywhere.12.0.1/dbusage/transact-s-4136352.html https://infocenter.sybase.com/help/index.jsp?topic=/com.syba... (old version but hasn't changed)
- plorkyeran 2y ago> Both transactions will run, but as soon as either touches a row the other has already modified […] The whole point of this scenario is that this never happens. The two writes touch unrelated sets of rows. There’s no point where they both try to modify the same row and a conflict is observed.
- magicalhippo 2y agoAh, implied assumptions strike again. I was thinking so DB that when I read "remove", I assumed that. Of course you're right, if you're not actually removing B and C, there's no conflict. If you actually remove B and C in the transaction, ie "deallocating" them either by deleting rows or updating prev/next/data to null, you'll get the conflict I mentioned.