SQLite Forum

Sqlite3 can happend deadlock?
Login

Sqlite3 can happend deadlock?

(1) By anonymous on 2021-12-10 05:22:42 [link] [source]

I am using sqlite3 as local cache data.

my php code write to a sqlite3 database file(randomly access from 20 files) today i found out a sever is hang up till every php process timeout.(60s) not all server. just one server, and just one of 20 servers.

i had Imagined everything can happen scenario.. but i only can imagine sqlite3 deadlock and no return resource.

I know, if php process is dead(by timeout) then return every file handler resource...

there are anything limitation to get connection sqlite3? (i think it happens very very very sometimes)

I am using sqlite 3.7.17 version.

It is sqlite3 cores' bug? or just I did something wrong?

(2) By AlexJ (CompuRoot) on 2021-12-10 06:59:52 in reply to 1 [link] [source]

Hi,

Don't get me wrong, but your question is a subject for https://stackoverflow.com/ Even so, you should provide your code there, so people wouldn't try to be a psycho to guess what is wrong with your code.

Try to replicate query via sqlite3 shell utility, try EXPLAIN to debug your query before assuming it's a sqlite's bug.

(5) By Ryan Smith (cuz) on 2021-12-10 14:19:09 in reply to 2 [link] [source]

... so people wouldn't try to be a psycho to guess what is wrong with your code.

I too feel that reading other people's code too much, really saps one's empathy. :)

(11) By anonymous on 2021-12-13 02:51:26 in reply to 2 [link] [source]

I know I know... I am not newbie..

this is not my code problem.

I just would like to know someone also met a like this problem and It can be happened from unknown factor (sunspot explosion, kicking hardware...) so don't return file handler resource..(it can happened or never can't or have a recovery)

till 256 hours no problem. but suddenly deadlock(can't access db till timeout 60s and doesn't work busy_timeout ) and every process restarted but never resolved...

I think it is happened from some hardware error but sqlite3 having not a recovery..(or linuxs problem?)

(3) By Gunter Hick (gunter_hick) on 2021-12-10 07:06:08 in reply to 1 [source]

Version 3.7.17 is extremely old, current is 3.37.

SQLite does not support multiple concurrent writers, they will have to take turns. If a writer does not complete it's transaction, it will stall all other processes, including readers. Similarly, a reader that does not complete it's transaction will stall any writer. Eventually, depending in your setting of the busy timeout, SQLite will return SQLITE_BUSY (indicating that it could not acquire the necessary lock) or SQLITE_DEADLOCK (indicating that SQLite already knows it can never acquire the necessary lock).

I later releases of SQLite, there is WAL mode that allows multiple readers to proceed even if a single writer is making changes; writers still need to take turns. This may help, but since details of what you are doing remain unclear, there is no guarantee.

Most probably you are mishandling the library.

(4) By Warren Young (wyoung) on 2021-12-10 09:46:08 in reply to 3 [link] [source]

Or his hardware is as old as his SQLite version implies, with disk failure causing I/O delays.

(6) By JayKreibich (jkreibich) on 2021-12-10 17:55:28 in reply to 1 [link] [source]

Yes, SQLite can deadlock. The application using SQLite needs to be written in a way that it avoids and/or breaks deadlocks. Most developers get this wrong.

The most common error is to set a busy-handler (via sqlite3_busy_timeout() ) and think that solves everything. It doesn't. If SQLite returns SQLITE_BUSY, sooner or later your application needs to rollback the current transaction, release all of its locks (breaking the deadlock), and start over whatever it was trying to do from the beginning.

(7) By Larry Brasfield (larrybr) on 2021-12-10 18:18:15 in reply to 6 [link] [source]

Would you agree that "SQLite can deadlock" would be more accurately stated as "SQLite can participate in deadlocks"?

If you are aware of any deadlocking SQLite can do on its own, please share.

(8) By JayKreibich (jkreibich) on 2021-12-10 20:39:42 in reply to 7 [link] [source]

You seem to have in mind a specific difference between those two statements, but I'm not sure I follow it.

I would say, "SQLite can deadlock" because a very simple, single-threaded application using the SQLite library can end up in a deadlock situation due to locks entirely created, held, and managed by the SQLite code, even when the API is being (mostly) correctly used.

Now, in the case of a "very simple, single-threaded application," that implies there is at least one other process (perhaps another copy of the same code) running elsewhere, and also trying to access the same database, but the statement still stands.

The "mostly" correct API usage comes only from the handling of an SQLITE_BUSY error. It is allowed and not-incorrect, when encountering an SQLITE_BUSY, to simply try the operation again. This is basically what the sqlite3_busy_timeout() handler attempts to do.

The issue is if the re-try attempt is in an infinite loop. When an SQLITE_BUSY is returned repeatedly, eventually the application needs to give up rollback the whole transaction, forcing SQLite to release all of its internal locks, and breaking the deadlock.

If that's an SQLite deadlock, or simply the "participation" in a deadlock, is something of a semantic argument. That said, I think the key points, however you want to label them, are:

  1. This type of deadlock is inherent in the design of SQLite's locking system. It is documented and expected, so it is not a bug, design-flaw, or other fault with SQLite or its code.

  2. The application code participates in both maintaining the deadlock AND in breaking it via the SQLite API.

  3. An SQLite deadlock turns into an application-level deadlock only if the application code finds itself in an infinite loop around the error handling code. That is, calling into an SQLite API call and simply never having it return is not what's going on.

In short, SQLite can find itself in a deadlock, but it is also aware that it is (likely) in a deadlock. The deadlock is only maintained if the application fails to react to it, but the application must take specific action to break the deadlock, or it will be maintained by default.

Chapter 7 of Using SQLite (O'Reilly Media, 2010) covers this in some detail on pages 152 - 155, although the book was released before WAL mode was a thing.

(9) By Larry Brasfield (larrybr) on 2021-12-10 21:31:13 in reply to 8 [link] [source]

Thanks, Jay, for your insight on this. (I purchased your book, years ago, and learned much from it. So thanks for that too. I was sorry to learn that a sequel is not looking rewarding enough to happen.)

I'm not fond of semantic quibbling, but I think that saying "SQLite can deadlock" is akin to saying that waiting for a semaphore (or anything else, really) can deadlock. And such waiting is done by an application, whose author(s) have stumbled into writing loops that may never terminate.

You've made very clear that it is up to applications to not use SQLite in a manner that results in deadlock. We could say the same about using a Linux semaphore, or a C "while" loop. But we do not say the "while" can deadlock or the semaphore can deadlock. They are tools which, if wielded carelessly, can create a deadlocking combination. SQLite is such a tool.

That's all I had in mind.

(10) By anonymous on 2021-12-13 02:17:19 in reply to 6 [link] [source]

really thanks. It is really make me creepy... :(... always trying to make something as multi processing from single thread platform makes some wrong...