SQLite Forum

Sqlite3 doesn't support row level locking?
Login

Sqlite3 doesn't support row level locking?

(1) By anonymous on 2021-11-23 09:37:00 [link] [source]

I am using sqlite3 for local cache layer for updating remote database server.

but sqlite3 insert so slow because it is locking all over the table.

can't use insert data like map reduce..

There aren't feature like row level locking like mysql InnoDB?

(2) By Stephan Beal (stephan) on 2021-11-23 09:48:46 in reply to 1 [link] [source]

but sqlite3 insert so slow because it is locking all over the table.

It locks the whole file, not individual tables:

https://www.sqlite.org/lockingv3.html

There aren't feature like row level locking like mysql InnoDB?

Nope. Note the "lite" part of the name.

(6) By anonymous on 2021-11-24 08:45:08 in reply to 2 [link] [source]

Yeah. file locking. thanks comment.

(3) By anonymous on 2021-11-23 18:49:25 in reply to 1 [source]

Are you inserting from multiple sources or a single source?

If from a single source then what you are complaining from is syncing on every insert, which is not that related to locking.

If you are inserting for multiple sources then locking will be an issue of course. May be you want to consider WAL mode with synchronous set to "Normal" but check if the durability guarantees fit your application requirements

(7) By anonymous on 2021-11-24 08:55:33 in reply to 3 [link] [source]

inserting from many threads by client request. i read documentation about WAL mode, but it is a little bit different.

I think sqlite3 can support row level locking by static file size db...(also have to create many files if db have many data.)

I just solved this problem. I just made many sqlite3 db(file) and insert randomly with timeout.

thanks comment.

(8) By Gunter Hick (gunter_hick) on 2021-11-24 15:17:04 in reply to 7 [link] [source]

If it is OK to lose cache contents on thread termination (and it is not necesary to share information between threads), then consider using one connection per thread and the special filenames ":memory:" for a private, in memory database, or "" (empty string) for a private, on disk database. You will have to create the table(s) after opening.

That way, each thread has its own database that magically disappears when the thread terminates. This is much less of a hassle than attempting to keep track of a flea bag full of disk files.

(4) By Simon Slavin (slavin) on 2021-11-23 21:49:06 in reply to 1 [link] [source]

Are you inserting many rows in one big task ? If so, combine them in a transaction, by putting BEGIN before the first INSERT and COMMIT after the last one.

(5) By anonymous on 2021-11-24 08:43:53 in reply to 4 [link] [source]

many threads insert to single file. but thanks your advice.

(9.1) Originally by AquaMack with edits by Stephan Beal (stephan) on 2021-11-26 14:13:00 from 9.0 in reply to 4 [link] [source]

Deleted