SQLite Forum

lock and low concurrency
Login

lock and low concurrency

(1) By sqlite3_preupdate_count (XiongZaiBingGan) on 2021-01-09 10:54:15 [link]

hi everybody, i'm reading the book <inside sqlite> that publish in April 15, 2007.in the book ,it says: "the sqlite lock is database file oriented.so if any one transaction is writing to any part of a database file, all other transactions are prevented from reading or writing any part of the file."

in most common case,one transaction want to read/write table_1 and another transaction read or write table_2.

anyone knows does sqlite support table-oriented lock in new version?

if sqlite doesn't support table-oriented lock ,how to improve the performance in case transaction operate in different table?

in my simple idea, make an agreement at application level: 1 database-file only contains 1 table,so the lock in different tables is lock in different database file.the concurrency performance is better. anyone knows method to improve the performance ?

(2) By Richard Hipp (drh) on 2021-01-09 14:05:50 in reply to 1 [link]

You might be interested in [WAL mode][1].

[1]: https://www.sqlite.org/wal.html

(3) By Simon Slavin (slavin) on 2021-01-10 13:26:26 in reply to 1

SQLite still has no table-level locks.  If it does need to lock anything, it locks the whole database.

Unfortunately your suggested fix doesn't help much.  Because your two databases will probably be on the same disk.  SQLite's calculations and procedures for handling database are extremely fast and efficient.  Deciding which parts of disk must be read or written doesn't take much time.  The thing that takes longest for SQLite is reading and writing to disk.  And if your two databases are on the same disk they cannot be read or written at the same time.

Before saying that performance should be improved, perhaps you should run some experiments and find out whether SQLite really is slow for your particular program.  SQLite in WAL mode is very fast, even compared with very expensive SQL databases.  This is partly because it requires no network traffic: it has no client-server model.  Your program reads and writes the disk itself.

(4) By Keith Medcalf (kmedcalf) on 2021-01-10 23:14:15 in reply to 1 [link]

> in most common case,one transaction want to read/write table_1 and another transaction read or write table_2.

Why does this need to occur side-by-each and not one-after-each?

True "side-by-each" is usually prohibitively expensive to obtain and in almost all cases is merely an illusion.