The timeout setting is not honored when a transaction is active (with example)

[jogc]

Bug report

Bug description:

The timeout argument to sqlite3.connect is not honored and a "sqlite3.OperationalError: database is locked" is triggered immediately if this script is invoked a second time while the first is still running (sleeping). This only happens when a
transaction is active - if the BEGIN statement is removed the bug is not triggered.

Expected behavior: the script should wait for up to 10 seconds for the first invocation to finish before giving up
Actual behavior: the script immediately crashes with "sqlite3.OperationalError: database is locked"

Python version: 3.11.2 (Debian: python3.11-minimal 3.11.2-6+deb12u3, libpython3.11-stdlib:amd64 3.11.2-6+deb12u3)
SQLite version: 3.40.1 (Debian: sqlite3 3.40.1-2)
OS: Debian GNU/Linux 12.7
Kernel: Linux 6.1.0-22-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.1.94-1 (2024-06-21) x86_64 GNU/Linux

#
# a table must be manually created first:
# sqlite3 testcase.db 'CREATE TABLE IF NOT EXISTS customers (a)'
# (if this is done by the script the bug is not triggered for some reason)
#

import sqlite3
import time

conn = sqlite3.connect(
 'testcase.db',
 isolation_level = None, # test case will work even if this is left out
 timeout = 10
)

c = conn.cursor()

print('starting transaction')
c.execute('''BEGIN''')
print('transaction started')

print('doing SELECT')
c.execute('SELECT * FROM customers', ())
print('did SELECT')

print('doing UPDATE')
c.execute('UPDATE customers SET a = 0 WHERE a = 999999', ())
print('did UPDATE')

print('sleeping')
time.sleep(30)
print('slept')

print('transaction ended')

CPython versions tested on:

3.11

Operating systems tested on:

Linux

[rafsaf]

See https://www.sqlite.org/lang_transaction.html#read_transactions_versus_write_transactions

If some other database connection has already modified the database or is already in the process of modifying the database, then upgrading to a write transaction is not possible and the write statement will fail with SQLITE_BUSY.

This looks like something expected on sqlite side, the provided script does exactly that and it breaks in this very moment of upgrade to write transaction

starting transaction
transaction started
doing SELECT
did SELECT
doing UPDATE
Traceback (most recent call last):
 File "/home/rafsaf/script.py", line 27, in <module>
 c.execute('UPDATE customers SET a = 0 WHERE a = 999999', ())
sqlite3.OperationalError: database is locked

if you revmoe this part of code:

print('doing SELECT')
c.execute('SELECT * FROM customers', ())
print('did SELECT')

it does wait 10s before giving OperationalError.

if you change "BEGIN" to "BEGIN IMMEDIATE", it also does wait 10s.

Hope that helps. In any case, if this answers your question feel free to close this issue.

[erlend-aasland]

This looks like something expected on sqlite side, the provided script does exactly that and it breaks in this very moment of upgrade to write transaction

Yes, I agree with Rafał; this is expected behaviour according to the SQLite docs. Closing as not-a-bug. Thanks for the report!