SQLite Forum

How to circumvent "apsw.BusyError: BusyError: database is locked"?
Login

How to circumvent "apsw.BusyError: BusyError: database is locked"?

(1) By 6kEs4Majrd on 2020-06-04 00:13:46 [link] [source]

I see this error.

apsw.BusyError: BusyError: database is locked

I've already used. So busy_timeout does not solve this problem.

pragma busy_timeout=2147483647;

What else can be done to circumvent this problem? Thanks.

(2) By Stephan Beal (stephan) on 2020-06-04 00:25:33 in reply to 1 [link] [source]

There are cases which a busy timeout cannot trigger and you might be hitting one of them. See https://www.sqlite.org/c3ref/busy_handler.html, fourth paragraph.

(3) By 6kEs4Majrd on 2020-06-04 02:11:55 in reply to 2 [link] [source]

In my application, I can be sure that there will be a deadlock between two processes. In such a case, what can be done to prevent BusyError from occurring. Thanks.

(4) By Gunter Hick (gunter_hick) on 2020-06-04 06:08:55 in reply to 3 [link] [source]

SQLITE_BUSY is SQLite's way of telling you that you are in a deadlock. You need to change your application to avoid the deadlock.

Since you don't divulge how you faithfully reproduce the deadlock, remedies cannot be suggested. Other than maybe using BEGIN IMMEDIATE so that upgrading transactions from READ to WRITE is avoided.

Look up the Dining Philosophers problem. Nobody gets to eat until one of them lets go of the one fork they already have. No amount of waiting will resolve the issue, which incidentally is the definition of deadlock.

(5) By 6kEs4Majrd on 2020-06-04 12:01:32 in reply to 4 [source]

I use the following code (n2d contains the data to be written, n2d of different processes are not guaranteed to be different, they may be partially the same of completely the same). I don't understand how deadlock is caused and how to solved it? I basically just write to the db (except checking if the table is avaialble), so by rewriting the code the deadlock could be removed? Thanks.

import apsw
conn = apsw.Connection(sys.argv[1])
c = conn.cursor()
c.execute('pragma busy_timeout=2147483647;')
c.execute('BEGIN TRANSACTION;')
try:
	c.execute('CREATE TABLE IF NOT EXISTS sqlar(name TEXT PRIMARY KEY, mode INT, mtime INT, sz INT, data BLOB)')
	for (name, len_data, zlib_data) in n2d:
		c.execute('REPLACE INTO sqlar VALUES(?, ?, ?, ?, ?)', [name, 0, 0, len_data, zlib_data])
	c.execute('COMMIT')
except:
	c.execute('ROLLBACK')
	raise

(6) By Keith Medcalf (kmedcalf) on 2020-06-04 14:09:21 in reply to 5 [link] [source]

When you commence a transaction that you know will write to the database use BEGIN IMMEDIATE rather than BEGIN DEFERRED.

This is preschool-level deadlock avoidance procedures. Always obtain all your required locks all at once and always in the same order, and release them all on failure to acquire a necessary lock.

(7) By 6kEs4Majrd on 2020-06-04 18:25:18 in reply to 6 [link] [source]

Now, I have multiple write processes using BEGIN IMMEDIATE. Then, I access the db for reading only using the following code.

import apsw
conn = apsw.Connection(f, flags = apsw.SQLITE_OPEN_READONLY)
c = conn.cursor()
c.execute('pragma busy_timeout=5000;')
try:
	for x in c.execute('SELECT name FROM sqlar'):
		print(x[0])
except:
	print(sys.argv[1] % ("Failed to process '%s'." % f), file=sys.stderr)
	raise

I still get the same error.

apsw.BusyError: BusyError: database is locked

Do you know how to fix the problem?

(8) By Keith Medcalf (kmedcalf) on 2020-06-04 23:32:45 in reply to 7 [link] [source]

Set your busy timeout longer.
Make sure you do not hold write transactions longer than you need to. (Only write in write transactions -- do not open them then go for a coffee break).
Consider using WAL to permit simultaneous reading and writing (though you are still limited to one writer at a time).

(9) By 6kEs4Majrd on 2020-06-05 02:27:02 in reply to 8 [link] [source]

So I need to add the following line to both the writing python apsw program and the reading python apsw program showed in the two previous messages in this thread? Or I just need to add this line to one of the two programs? Thanks.

PRAGMA journal_mode=WAL;

(10) By Keith Medcalf (kmedcalf) on 2020-06-05 03:38:42 in reply to 9 [link] [source]

The WAL setting is persistent (stored in the database), so you really only need to set it once when you create the database. However, changing the journal_mode to WAL when it is already WAL is not harmful.

(11) By 6kEs4Majrd on 2020-06-05 05:15:58 in reply to 10 [link] [source]

What about changing journal_mode from WAL to default? Is it harmful?

For the script, I don't know what I should test to conditionally use PRAGMA journal_mode=WAL;. Do you know how the code should be changed?

import apsw
conn = apsw.Connection(sys.argv[1])
c = conn.cursor()
c.execute('pragma busy_timeout=5;')
c.execute('PRAGMA journal_mode=WAL;')
c.execute('BEGIN IMMEDIATE;')
try:
        c.execute('CREATE TABLE IF NOT EXISTS sqlar(name TEXT PRIMARY KEY, mode INT, mtime INT, sz INT, data BLOB)')
        for (name, len_data, zlib_data) in n2d:
                c.execute('REPLACE INTO sqlar VALUES(?, ?, ?, ?, ?)', [name, 0, 0, len_data, zlib_data])
        c.execute('COMMIT')
except:
        c.execute('ROLLBACK')
        raise

(12) By Keith Medcalf (kmedcalf) on 2020-06-05 06:26:23 in reply to 11 [link] [source]

If you execute pragma journal_mode; you will get back the journal_mode in effect. Actually, when you execute pragma journal_mode=WAL; you will get back the journal mode in effect after the command is executed.

if c.execute('pragma journal_mode;').fetchone()[0] <> 'wal':
   if c.execute('pragma journal_mode=wal;').fetchone()[0] <> 'wal':
      raise apsw.Error('Cannot change database to WAL')

You can of course skip the testing and just set the journal_mode and see if it worked.

if c.execute('pragma journal_mode=wal;').fetchone()[0] <> 'wal':
      raise apsw.Error('Cannot change database to WAL')

Changing the database journal_mode is never harmful. If you change it to something you do not want to change it to, you may be unhappy with the result, but it will not harm anything. That is, it will cause the expected result.

(15) By 6kEs4Majrd on 2020-06-05 14:00:28 in reply to 12 [link] [source]

I got this error.

  File "/xxx.py", line 23, in <module>
    c.execute('PRAGMA journal_mode=WAL;')
  File "src/cursor.c", line 1019, in APSWCursor_execute.sqlite3_prepare
  File "src/statementcache.c", line 386, in sqlite3_prepare
apsw.BusyError: BusyError: database is locked

So the following code would also cause the same error?

if c.execute('pragma journal_mode=wal;').fetchone()[0] <> 'wal':
      raise apsw.Error('Cannot change database to WAL')

So this is better as the first if just read the journal_mode and would not cause apsw.BusyError? Since WAL mode is persistent, as it as long as it is set once, the first if will always be false then the rest pragma journal_mode=wal; statement will not be called. Hence, there will be much less chance to see apsw.BusyError?

if c.execute('pragma journal_mode;').fetchone()[0] <> 'wal':
   if c.execute('pragma journal_mode=wal;').fetchone()[0] <> 'wal':
      raise apsw.Error('Cannot change database to WAL')

(13) By TripeHound on 2020-06-05 06:48:39 in reply to 11 [link] [source]

I notice you've got a busy_timeout of 5... if you look at the documentation, you will see this is in milliseconds. A value of 5000 (=5 seconds) or more is probably what you want.

(14) By 6kEs4Majrd on 2020-06-05 13:56:19 in reply to 13 [link] [source]

I notice you've got a busy_timeout of 5... Thanks. I knew it. It was a typo.