Explicit locking mechanism in SQLite

Question

I did not find explicit sqlite locking commands before inserting or updating rows into the table. Does sqlite handle the locking mechanism on it own? The pager module described in http://sqlite.org/lockingv3.html handles the locking mechanism. But I am not sure if there are any commands that the user can use to explicitly lock the tables. Please advice.

Thanks

Answer 1

SQLite does whatever locking is necessary in order to implement the transaction scheme that your SQL statements describe. In particular, if you don't describe any then you get auto-commit behavior, with a lock held for the duration of each statement and then dropped as the statement finishes. Should you need longer transactions (often true!) then you ask for them explicitly with BEGIN TRANSACTION (often shortened to BEGIN) and finish with COMMIT TRANSACTION (or ROLLBACK TRANSACTION). The transaction handling is frequently wrapped for you by your language interface (as this makes it considerably easier to get right, coupling the transaction lifetime to a code block or method call) but at the base level, it comes down to BEGIN/COMMIT/ROLLBACK.

In short, you've got transactions. Locks are used to implement transactions. You don't have raw locks (which is a good thing; they're rather harder to get right than you might think from first glance).

Answer 2

As far as I know there are no dedicated sqlite commands to control locking. However you can get sqlite to lock the database using create transaction. For instance:

BEGIN IMMEDIATE TRANSACTION;
...
COMMIT TRANSACTION;

BEGIN EXCLUSIVE TRANSACTION;
...
COMMIT TRANSACTION;

If you read the documentation I linked you should get a better idea on the difference between IMMEDIATE & EXCLUSIVE transactions.

It might be worth noting that the locks in sqlite apply to the whole database and not just individual tables, unlike the LOCK TABLE statement in other sql databases.