It doesn't even work, when using PRAGMA busy_timeout = milliseconds;

Also:

• The timeout in Python does seem to work, if the transaction was started (but not finished) by sqlite3.
• And it works with both DEFERRED and EXCLUSIVE transactions (in sqlite3).

I tried a bit more to debug this:

• first adding some debugging output:

  def d(s):
      print(f"SQL: {s}")
  

  and later:

  con.set_trace_callback(d)
  

  But unlike the documentation seems to imply no messages are generated from any statements that result from the transaction management (otherwise I'd expect to see some BEGIN DEFERRED; or so.

• when using autocommit=True, which the docs recommend against, it does work as expected, when also adding some manual transactions like in:

  cur.execute("BEGIN DEFERRED;")
  x = cur.execute("INSERT INTO locks (name) VALUES (?);", (sys.argv[1],) )
  # ... the sleep, etc.
  cur.execute("COMMIT;")
  

  Interestingly, this does however not work, when I do the BEGIN… before the CREATE TABLE... no idea why,... cause it doesn't seem as if the sqlite3 utility would behave here as Python does.

In the OP you start both Python scripts using autocommit=False, implying implicitly opening transaction in both processes. OTOH, in your SQLite shell example, you open a transaction in the first process; for the second process, you seem to rely on SQLite's autocommit feature. If I repeat your SQLite shell exercise using explicit transaction management (similarly to what autocommit=False does implicitly), I get "Runtime error: database is locked (5)". Perhaps I'm misunderstanding what you are trying to do here. Perhaps you could try to reduce your repro to an absolute minimum, both for the Python script and the SQLite shell script.

But unlike the documentation seems to imply no messages are generated from any statements that result from the transaction management (otherwise I'd expect to see some BEGIN DEFERRED; or so.

You won't be able to see the initial BEGIN DEFERRED when you're using PEP-249 compliant transaction management; a transaction is implicitly opened when you create the sqlite3.Connection object. IOW, your .set_trace_callback call is always too late.

when using autocommit=True, which the docs recommend against [...]

Where in the docs do we explicitly recommend against using autocommit=True? IIRC, we only recommend autocommit=False. autocommit=True is perfectly fine (it is often my preferred setting).

The reason we recommend autocommit=False is because the sqlite3 stdlib module aims to be PEP-249 compliant.

The only problematic setting is the current default: autocommit=None.

In the OP you start both Python scripts using autocommit=False, implying implicitly opening transaction in both processes. OTOH, in your SQLite shell example, you open a transaction in the first process; for the second process, you seem to rely on SQLite's autocommit feature.

autocommit=False means not to use SQLite’s autocommit, but Python does it's own implicit transaction management, right?

In the 2nd shell process, it uses SQLite’s autocommit but that doesn't really change anything (other than also opening a transaction), or does it?

You can just change the 2nd shell to also use an explicit BEGIN and it will still block for the time of the .timeout whereas it doesn't within Python.

If I repeat your SQLite shell exercise using explicit transaction management (similarly to what autocommit=False does implicitly), I get "Runtime error: database is locked (5)".

I do so, too, but only after the timeout of 10s has passed (and I don't get it, if I release the lock by ending the transaction in the the shell). In Python I get it immediately.

Perhaps I'm misunderstanding what you are trying to do here. Perhaps you could try to reduce your repro to an absolute minimum, both for the Python script and the SQLite shell script.

I think the shell one is already at the absolute minimum.... and well the Python one more or less to.

The point simply is, on the shell/sqlite3 the timeout is waited and only afterwards I get a "locked" error. In Python it doesn't wait for the timeout to pass (and thus has no chance that the lock gets released meanwhile).

Where in the docs do we explicitly recommend against using autocommit=True?

Okay you don't actively recommend against it, but you do recommend for False ;-) ... which is kinda similar.

Okay you don't actively recommend against it, but you do recommend for False ;-) ... which is kinda similar.

I profoundly disagree with this.

You can just change the 2nd shell to also use an explicit BEGIN and it will still block for the time of the .timeout whereas it doesn't within Python.

I did, and I got the "database is locked" error.

I did, and I got the "database is locked" error.

Immediately or after 10s?

The point/bug is... in Python, the timeout is ignored, in sqlite3 it's not.

The point/bug is... in Python, the timeout is ignored, in sqlite3 it's not.

Since we're directly calling sqlite3_busy_timeout immediately after opening a connection, I seriously doubt that it is ignored:

BTW:

• sqlite3: the Python stdlib module
• SQLite: the underlying SQLite library

I tried your repro again, with added "fprintf debugging" for the sqlite3_open_v2 result code:

$ time ./python.exe busy.py 1st_a 5 0 10
con tmout: 5
rc=0
1st sleep: 0
[]
2nd sleep: 10
./python.exe busy.py 1st_a 5 0 10  0.04s user 0.01s system 0% cpu 10.070 total
$ time ./python.exe busy.py 2nd_a 500 0 0
con tmout: 500
rc=0
1st sleep: 0
[]
2nd sleep: 0
./python.exe busy.py 2nd_a 500 0 0  0.03s user 0.02s system 0% cpu 10.327 total

This harmonises with your expectations from the OP, quoting:

I'd expect the second to wait for 500s and as the first sleeps only 10s, it should succeed.

Perhaps the issue you are seeing is in your environment?

I should note that the two shell commands in #130971 (comment) were run in parallel in separate shells.

I've also extended my test prog lock.py a bit as follows:

#!/usr/bin/python3

import sqlite3
import sys
import time

#def d(s):
#    print(f"CAL: {s}")

print(f"{time.time()} connect timeout: " + sys.argv[2])
print(f"{time.time()} connect...")
con = sqlite3.connect("locks.db", autocommit=False, timeout=float(sys.argv[2]))
print(f"{time.time()} connected")
#con.set_trace_callback(d)
cur = con.cursor()

print(f"{time.time()} 1st sleep: " + sys.argv[3])
time.sleep(int(sys.argv[3]))
print(f"{time.time()} 1st sleep over")

#x = cur.execute(f"PRAGMA busy_timeout;")
#print(x.fetchall())
print(f"{time.time()} CREATE...")
cur.execute("CREATE TABLE IF NOT EXISTS locks (name TEXT PRIMARY KEY ON CONFLICT ROLLBACK) STRICT")

print(f"{time.time()} INSERT...")
try:
    x = cur.execute("INSERT INTO locks (name) VALUES (?);", (sys.argv[1],) )
except Exception as e:
    print(f"{time.time()} exception")
    raise e
    
print(x.fetchall())

print(f"{time.time()} 2nd sleep: " + sys.argv[4])
time.sleep(int(sys.argv[4]))
print(f"{time.time()} 2nd sleep over")

con.commit()
print(f"{time.time()} COMMITTED")

cur.close()
con.close()
print(f"{time.time()} closed"

Now when I delete locks.db and start the program twice (nearly at the same time) in different shells, I get:

$ ./lock.py 1st_a 5 0 10
1744074285.6948977 connect timeout: 5
1744074285.694912 connect...
1744074285.6950576 connected
1744074285.695063 1st sleep: 0
1744074285.6951218 1st sleep over
1744074285.695124 CREATE...
1744074285.6953359 INSERT...
[]
1744074285.6953502 2nd sleep: 10
1744074295.6956239 2nd sleep over
1744074295.7404013 COMMITTED
1744074295.7407506 closed

and

$ ./lock.py 2nd_a 500 0 0
1744074285.9952157 connect timeout: 500
1744074285.9952326 connect...
1744074285.995318 connected
1744074285.9953225 1st sleep: 0
1744074285.9953816 1st sleep over
1744074285.9953847 CREATE...
1744074295.755855 INSERT...
[]
1744074295.7561233 2nd sleep: 0
1744074295.7561977 2nd sleep over
1744074295.7840018 COMMITTED
1744074295.7841923 closed

As you can see, the timeout does indeed work here.

Next, I don't delete the DB but keep the existing one, and I only change the 1st arguments of both invocations in order to not fail the constraints because the PKEY already exists:

$ ./lock.py 1st_b 5 0 10
1744074344.4593296 connect timeout: 5
1744074344.4593499 connect...
1744074344.4594378 connected
1744074344.459442 1st sleep: 0
1744074344.4595008 1st sleep over
1744074344.4595032 CREATE...
1744074344.459598 INSERT...
[]
1744074344.4597077 2nd sleep: 10
1744074354.45983 2nd sleep over
1744074354.5024867 COMMITTED
1744074354.5026805 closed

and

$ ./lock.py 2nd_b 500 0 0
1744074344.829874 connect timeout: 500
1744074344.8298872 connect...
1744074344.829988 connected
1744074344.829991 1st sleep: 0
1744074344.830049 1st sleep over
1744074344.8300517 CREATE...
1744074344.8301365 INSERT...
1744074344.8301508 exception
Traceback (most recent call last):
  File "/home/calestyo/prj/ssh-askpass-auto/src/lock/./lock.py", line 31, in <module>
    raise e
  File "/home/calestyo/prj/ssh-askpass-auto/src/lock/./lock.py", line 28, in <module>
    x = cur.execute("INSERT INTO locks (name) VALUES (?);", (sys.argv[1],) )
sqlite3.OperationalError: database is locked

Here the timeout is not obeyed, and the 2nd invocation fails immediately.

Did you try with a fresh DB every time? Or also be re-using an existing one, and just changing the lock name (i.e. 1st_a, 1st_b, 2nd_a and 2nd_b)?

Perhaps the issue you are seeing is in your environment?

Hmm, difficult to rule out. I mean I use the normal Python and sqlite (lib/util) packages from Debian sid, I don't have any sqlite specific env vars or so set... nothing that really pops into my mind.

Thanks,
Chris.

The timeout works again, if one comments the line:

cur.execute("CREATE TABLE IF NOT EXISTS locks (name TEXT PRIMARY KEY ON CONFLICT ROLLBACK) STRICT")

and re-uses the already existing DB, then with 1st_c/2nd_c.

I've checked the same with the sqlite3 utility, again with an already existing DB,... i.e. doing:

$ sqlite3 locks.db
sqlite> BEGIN DEFERRED;
sqlite> CREATE TABLE IF NOT EXISTS locks (name TEXT PRIMARY KEY ON CONFLICT ROLLBACK) STRICT;
sqlite> INSERT INTO locks (name) VALUES ('1st_e');

waiting there and at the same time doing:

$ sqlite3 locks.db
sqlite> .timeout 10000
sqlite> BEGIN DEFERRED;
sqlite> CREATE TABLE IF NOT EXISTS locks (name TEXT PRIMARY KEY ON CONFLICT ROLLBACK) STRICT;
sqlite> INSERT INTO locks (name) VALUES ('2nd_e');

With the BEGIN DEFERRED in both, the 2nd also immediately gives a Runtime error: database is locked (5), just as in Python.
Without the BEGIN DEFERRED in the 2nd, the INSERT of the 2nd waits for the timeout.

So if Python does(?) a BEGIN DEFERRED then I guess the bug (if any at all) is rather in sqlite itself?

Cheers,
Chris.

I shall add for the records why I think it might be a bug in sqlite3: It's really the CREATE TABLE that makes the difference:

Even with the BEGIN DEFERRED in the 2nd, the timeout is obeyed as long as there's no CREATE TABLE in it.

I've forwarded the potential issue upstream.

From my side we can probably close the bug here.