Lock Handling

SQLite Lock Usage
Lock Usage with the DB SQL Interface

There is a difference in how applications written for the BDB SQL interface handle deadlocks as opposed to how deadlocks are handled for SQLite applications. For the SQLite developer, the following information is a necessary review in order to understand how the BDB SQL interface behaves differently.

From a usage point of view, the BDB SQL interface behaves in the same way as SQLite in shared cache mode. The implications of this are explained below.

SQLite Lock Usage

As mentioned previously in this chapter, SQLite locks the entire database while performing a transaction. It also has a locking model that is different from the BDB SQL interface, one that supports multiple readers, but only a single writer. In SQLite, transactions can start as follows:

  • BEGIN

    Begins the transaction, locking the entire database for reading. Use this if you only want to read from the database.

  • BEGIN IMMEDIATE

    Begins the transaction, acquiring a "modify" lock. This is also known as a RESERVED lock. Use this if you are modifying the database (that is, performing INSERT, UPDATE, or DELETE). RESERVED locks and read locks can co-exist.

  • BEGIN EXCLUSIVE

    Begins the transaction, acquiring a write lock. Transactions begun this way will be written to the disk upon commit. No other lock can co-exist with an exclusive lock.

The last two statements are a kind of a contract. If you can get them to complete (that is, not return SQLITE_LOCKED), then you can start modifying the database (that is, change data in the in-memory cache), and you will eventually be able to commit (write) your modifications to the database.

In order to avoid deadlocks in SQLite, programmers who want to modify a SQLite database start the transaction with BEGIN IMMEDIATE. If the transaction cannot acquire the necessary locks, it will fail, returning SQLITE_BUSY. At that point, the transaction falls back to an unlocked state whereby it holds no locks against the database. This means that any existing transactions in a RESERVED state can safely wait for the necessary EXCLUSIVE lock in order to finally write their modifications from the in-memory cache to the on-disk database.

The important point here is that so long as the programmer uses these locks correctly, he can assume that he can proceed with his work without encountering a deadlock. (Assuming that all database readers and writers are also using these locks correctly.)

Lock Usage with the DB SQL Interface

When you use the BDB SQL interface, the lock usage is considerably different. First, you cannot specify the kind of a lock that you want when you begin a transaction. That is, the IMMEDIATE and EXCLUSIVE keywords are ignored by the BDB SQL interface. Instead, you simply begin your transaction with BEGIN.

Note that this does not mean that Berkeley DB only supports one kind of a lock. Instead, Berkeley DB decides what kind of a lock you need based on what you are doing to the database. If you perform an action that is read-only, it acquires a read lock. If you perform a write action, it acquires a write lock. What you do not have to do (and, in fact, cannot do), is identify the type of lock that you want when you begin your transaction.

Also, the BDB SQL interface supports multiple readers and multiple writers. This means that multiple transactions can acquire locks as long as they are not trying to modify the same page. For example:

Session 1:

dbsql> create table a(x int);
dbsql> begin;
dbsql> insert into a values (1);
dbsql> commit; 

Session 2:

dbsql> create table b(x int);
dbsql> begin;
dbsql> insert into b values (1);
dbsql> commit; 

Because these two sessions are operating on different pages in the Berkeley DB cache, this example will work. If you tried this with SQLite, you could not start the second transaction until the first had completed.

However, if you do this using the BDB SQL interface:

Session 1:

dbsql> begin;
dbsql> insert into a values (2);

Session 2:

dbsql> begin;
dbsql> insert into a values (2); 

The second session blocks until the first session commits the transaction. Again, this is because both sessions are operating on the same database page(s). However, if you simultaneously attempt to write pages in reverse order, you can deadlock. For example:

Session 1:

dbsql> begin;
dbsql> insert into a values (3);
dbsql> insert into b values (3);
Error: database table is locked

Session 2:

dbsql> begin;
dbsql> insert into b values (3);
dbsql> insert into a values (3);
Error: database table is locked

What happens here is that Session 1 is blocked waiting for a lock on table b, while Session 2 is blocked waiting for a lock on table a. The application can make no forward progress, and so it is deadlocked.

The proper thing for your application to do here is to rollback the transaction for one of the sessions and then retry the operation. This is exactly what you would do if you were using SQLite in shared cache mode.