> Not sure if this is the problem you are running into, but it might be > because SQLite is detecting a potential deadlock.
I think that's exactly what's going on. SQLite is not bothering with the busy handler since it already knows no amount of waiting will solve the problem. > When you just use BEGIN, SQLite will acquire locks lazily - the first > read operation will acquire a shared lock, and the first write operation > will acquire a reserved lock. If I understand him correctly, drh said in another email that I could try using BEGIN IMMEDIATE rather than unqualified BEGIN to signal my intent to eventually do a write even though my first operation is a read. You say the same thing. > 1. Connection A begins a transaction. No locks acquired yet. > 2. Connection B begins a transaction. No locks acquired yet. > 3. Connection A reads from the database. It acquires a shared lock. > 4. Connection B begin a transaction. No locks acquired yet. > 5. Connection B writes to the database. It acquires a reserved lock. (5) is the point where your example deviates from what my example program is actually doing. My example program always starts with a read. Thus, both connections will first try to acquire a shared lock. > 6. Connection A writes to the database. It tries to acquire an exclusive > lock. But connection B already has one. Wouldn't connection A try to acquire a reserved lock? It's not going to go for the exclusive lock (by way of pending) until the COMMIT comes along. But nevertheless, in your example, A can't get reserved because B already has it. > Presumably, connection B would try to actually commit to the database at > some point. But doing so would require it to wait for all shared locks > to be released. Connection A is holding a shared lock. So now we could > have a deadlock - A is waiting for B so it can acquire a reserved lock, > and B is waiting for A so it can acquire an exclusive lock. SQLite > detects this and connection A immediately returns SQLITE_BUSY, bypassing > any busy timeout or busy handler. I'm thinking one possible sequence might be: 1. A and B both run BEGIN. 2. A and B both run SELECT and both acquire SHARED. 3. A runs INSERT and acquires RESERVED. 4. B runs INSERT, but it can't acquire RESERVED because of A. B is now blocked, and the busy handler is invoked. 5. A runs COMMIT and acquires PENDING. However, it can't acquire EXCLUSIVE because B still holds SHARED. SQLite breaks this deadlock by smiting A with SQLITE_BUSY, at which point A is expected to give up and let B proceed. > To avoid this issue, use "BEGIN IMMEDIATE" instead of "BEGIN". This > makes it so the writer immediately acquires a reserved lock. Since it > jumps directly there instead of acquiring a shared lock first, there's > no deadlock potential, so the busy timeout will actually be respected. Like this? 1. A runs BEGIN IMMEDIATE and acquires RESERVED. 2. B runs BEGIN IMMEDIATE but can't acquire RESERVED. B is now blocked, and the busy handler is invoked. 3. A runs SELECT and INSERT. 4. A runs COMMIT and acquires PENDING and EXCLUSIVE. 5. A completes and drops all locks. B is now free to run. 6. B does everything A just did. ------------------------------------------- CONFIDENTIALITY NOTICE: This email and any attachments are for the sole use of the intended recipient and may contain material that is proprietary, confidential, privileged or otherwise legally protected or restricted under applicable government laws. Any review, disclosure, distributing or other use without expressed permission of the sender is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies without reading, printing, or saving.. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
