On Sunday, 20 January, 2019 17:19, Simon Slavin <slav...@bigfraud.org> wrote:
>> If the issue is the dead lock, you get similar issues with all >> DBMSes. > I'm not perfectly sure of my logic here, but OP posted elsewhere that > replacing BEGIN with BEGIN IMMEDIATE cures the problem. I think this > indicates that his problem isn't deadlock. This indicates that the problem was indeed a deadlock. A deadlock (in this case) occurs when there are two transactions in progress, each of them has a SHARED (READ) lock, one of them (we will call it "A") successfully upgrades to a PENDING lock on the way to an EXCLUSIVE (WRITE) lock, and the other transaction (which we shall call "B") rather than releasing the SHARED lock to permit the PENDING LOCK to upgrade to EXCLUSIVE (WRITE) status, instead tries to acquire a PENDING lock on the way to an EXCLUSIVE (WRITE) lock. The EXCLUSIVE lock cannot be granted to A until the SHARED lock is released by B. However, instead of releasing the SHARED lock, B has also tried to acquire a PENDING lock on the way to an EXCLUSIVE lock. If the PENDING lock is granted to B then you will have two processes that are "deadlocked" (also known as a Mexican Standoff or any number of other colourful names) each waiting forever for the other to give up its SHARED lock so it can proceed. Since this outcome would be double-plus-ungood, the second transaction (B) is terminated by tossing it to the wolves (Return SQLITE_LOCKED or some such error to indicate a deadlock situation). This will cause the second transaction (B) to roll-back and release its SHARED lock permitting A to proceed to completion. (The SHARED lock must also be forced to be released because it represents a lock against a state which no longer exists now that A has been permitted to proceed). >The problem is more about how SQLite implements BEGIN without >IMMEDIATE. As others have explained in this thread, SQLite does its >best to avoid locking the database until it really needs to. But it >does need to obtain a shared lock, and this fails in the specific >pattern OP uses. >Which is why BEGIN IMMEDIATE exists. So use BEGIN IMMEDIATE. Done. The procedure to be used in order to avoid deadlocks in multiprocessing code has been known for a long time. To avoid deadlocks, concurrent code must be able to acquire all the locks it requires at once. The "at once" part can be spread out in time by always acquiring the same set of locks in the same order. If a process fails to acquire any one of them it must relinquish all the locks it has obtained before trying again (that is, it must rebuild its state anew from nothingness). BEGIN IMMEDIATE acquires the SHARED and WRITE lock from the get-go and if successful cannot be a "deadlock victim" since it has acquired all the locks it will ever need. A bare BEGIN, however, can be a deadlock victim since it has only acquired a SHARED lock and if it tries and fails to acquire an additional (WRITE) lock it does not hold but cannot obtain, then it must relinquish all locks (including the SHARED lock) before being permitted to try again. Fully opportunistic locking systems (such as you get in network filesystems or in database servers) often randomly choose the victim in order to avoid deadlocks unless they are able to determine an appropriate victim. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
