What does BEGIN IMMEDIATE/EXCLUSIVE do in Berkeley DB?
These special transaction modes are used in SQLite to avoid deadlock. Internally, SQLite has the notion of “read-only” vs “update” transactions. These keywords apply to update transactions, and fully understanding them requires understanding SQLite locking. In Berkeley DB, these keywords are mostly ignored. That is, they are not required to avoid deadlock (nor do they prevent deadlocks, but such deadlocks are automatically detected by Berkeley DB). The effect of using either keyword is that the transaction starts as an update transaction, which causes exclusive locking for reads (similar to SELECT FOR UPDATE in other RDBMSs). This can reduce the frequency of deadlocks in some applications.
These special transaction modes are used in SQLite to avoid deadlock. Internally, SQLite has the notion of read-only vs update transactions. These keywords apply to update transactions, and fully understanding them requires understanding SQLite locking. In Berkeley DB, these keywords are mostly ignored. That is, they are not required to avoid deadlock (nor do they prevent deadlocks, but such deadlocks are automatically detected by Berkeley DB). The effect of using either keyword is that the transaction starts as an update transaction, which causes exclusive locking for reads (similar to SELECT FOR UPDATE in other RDBMSs). This can reduce the frequency of deadlocks in some applications.
