Aiosqlite returns instantly Database is Locked error, even if timeout is 30 s

[qarmin]

Description

When running random inserting, deleting, selecting, updating in app that is running multiple times in pararrel, then sometimes I got "Database is Locked" error.

I set database timeout time to 30s

I run app in parallel with this code (12 means number of threads)

seq 12 | parallel -u -N0 -P12 python3 scratch_1.py | grep -E 'took|locked|EXCEPTION|Error' |& tee  aiosqlite_none.txt

When I run app, after a while I see that some of commands exeutes in > 10s when most of the time rest operations take usually < 10ms to execute

140131560632768 delete - took 38.721323013305664 ms
140131560632768 update - took 4.200935363769531 ms
140131560632768 delete - took 4.871129989624023 ms
140131560632768 insert - took 11.445760726928711 ms
140131560632768 delete - took 5.630016326904297 ms
140131560632768 delete - took 4.244804382324219 ms
140131560632768 select - took 3.5469532012939453 ms
140131560632768 select - took 2.2268295288085938 ms
140131560632768 delete - took 16563.55047225952 ms

This is likely expected with sqlite, because same problems I got with python sqlite and rust diesel sqlite versions.

The problem is, that sometimes I got instant sqlite3 operational error(Database is locked message)

139769081631168 update - took 1.9054412841796875 ms
139769081631168 update - took 1.8732547760009766 ms
139769081631168 insert - took 1.6071796417236328 ms
139769081631168 insert - took 1.1374950408935547 ms
FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF insert - took 4006.369113922119 ms
GOT EXCEPTION, trying again - retries number 2   <------ Here database shows, that first time executing command  was unsuccessful(wait time before next check is 2s)
GOT EXCEPTION, trying again - retries number 1 <------ Another unsuccessful executing and again 2s sleep before next check
GOT EXCEPTION, no more to try
EXCEPTION database is locked OperationalError 140684239561152 delete - took 4006.1073303222656 ms <-------- 4s = 2s sleep + 2s sleep + 6ms executing - so there is missing 30s timeout
140684239561152 delete - took 4006.4618587493896 ms
FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF delete - took 4006.4618587493896 ms

There is no 30s timeout here, but should be.

Almost same code I tried with python sqlite and rust diesel sqlite and I never had similar problems, so that is why I think that this is a problem with this library

Code

• aiosqlite version with problems - aiosqlite.py.txt
• sqlite version without problems - sqlite.py.txt

Results

• aiosqlite time results - aiosqlite_none.txt
• asqlite time results - sqlite_none.txt

Details

• OS: Ubuntu 22.04
• Python version: 3.10
• aiosqlite version: 0.19.0/0.18.0
• Can you repro on 'main' branch? - not tried yet, but almost sure that still happens there
• Can you repro in a clean virtualenv? - yes, with python 3.9, but I had same problem

[jbaiter]

I'm encountering the same issue, I have both the timeout parameter on the connect call and the PRAGMA busy_timeout set to 30 seconds, but when concurrent writes (from multiple threads with each their own connection) happen, .execute returns immediately with a Database is locked