CODEWITHSUNDEEP
databasesqlite
Manu Manjunath
Manu Manjunath

Reputation: 6411

How to achieve row-level locking on an in-memory SQLite db?

I'm running SQLite v3.7.17 from my program in in-memory mode and using shared cache (as specified in Shared Cache And In-Memory Databases). My program is multi-threaded and all these threads access the same in-memory database.

Is there any way I can configure or use my SQLite database such that, when two threads run update query on same table (but different rows), one doesn't wait on another? That is, how can I achieve row-level locking on this in-memory db?

This should theoretically be possible as my SQLite data is not in a file (therefore filesystem writes do not apply).

Upvotes: 10

Views: 12963

Answers (4)

JaeIL Ryu
JaeIL Ryu

Reputation: 189

sqlite3 doesn't support row level lock. if you can guarantee item is unique or don't need to be unique then you can works with multiple files.

just make multiple sqlite3 database(files) and open it. if you make 50 db, then you can works with 50 rows in same time.

Upvotes: 1

binhgreat
binhgreat

Reputation: 1002

Sqlite does not support row lock feature. And i’ve just seen sqlumdash, which is based on Sqlite has row lock feature.

Please check it at: https://github.com/sqlumdash/sqlumdash/

As I know, it’s developed by toshiba

Upvotes: 5

Martin
Martin

Reputation: 11

Is this in a larger transnational scenario? Because if the situation is as simple as you describe, then there is no advantage to row-locking vs. table locking.

An in-memory DB isn't subject to I/O latency; it is CPU bound and the CPU can process the two writes sequentially faster than it could process them concurrently because the latter has all the same memory operations plus thread-swapping and row-locking overhead. Sure, in a multi-CPU system one could, theoretically, write to different rows simultaneously, but the necessary logic to support row-locking would actually take longer than the (trivial) operation of writing the record into memory.

In an IMDB of any size, table-locks on individual tables can be retained for efficiency while multiple CPUs can be employed simultaneously on multiple independent queries against multiple independent tables.

Upvotes: 1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95562

It's not the filesystem that determines whether SQLite can lock rows. It's SQLite's architecture.

Even using write-ahead logging, you can only have one writer at a time.

Writers merely append new content to the end of the WAL file. Because writers do nothing that would interfere with the actions of readers, writers and readers can run at the same time. However, since there is only one WAL file, there can only be one writer at a time.

SQLite3 has a kind of table locking now, but not row locking.

Upvotes: 9

Related Questions