On Thu, 8 Dec 2005, John Stanton wrote:
>My understanding of Sqlite is that transactions are locked by locking
>the entire database. Deadlocks can occur and the programmer must be
>aware of the possibility of a transaction temporarily failing due to a
>lock and of the need to possibly resolve deadlocks if the application
>makes them possible. This behaviour is a feature of the absence of a
>central DBMS server process. Sqlite runs in each user process, a major
>reason for its simplicity and wide appeal.
Centrally managed DBMS can also have deadlocks. Consider this sequence of
events:
1. App 1 reads row X
2. App 2 reads row Y.
3. App 1 updates row Y.
4. App 2 updates row X.
With row locks, we'll get a deadlock at 4 as App 1 already has a read
lock on row X, but App 1 is blocked waiting for a write lock on row Y, and
so cannot proceed to commit or rollback its transaction. This scenario is
causes the SQLITE_BUSY in SQLite, but will also deadlock a row locking
DBMS. It is the detection of this that complicates the issue for row
locking DBMS.
>
>By "enterprise" I meant the usual IT definition, a step up from
>"department".
>
>If you are interested in the complexity of MVCC take a look at the
>source of PostgreSQL.
>
>One other point, and not a hostile one, is the conception of a lock.
>You seem a little confused between internal synchronisation and locks
>set by the DBMS for the benefit of applications.
>
No. Just overloading the use of the term lock. In a central server based
DBMS, much of what would require file locking in SQLite can be achieved
using lighter, quicker IPC primitives. PostgreSQL uses semaphores for
coordination between servers. Such locks are much quicker and fine grained
than file locks. But it is still a lock, just as a file lock is a lock as
well. All locks are used for synchronisation. Internal or external is just
a matter of boundaries.
In summary:
- SQLite uses file locks to coodinate concurrent access at the database
level.
- Row locking central DBMS uses row read/write locks to coordinate access
at the row level. Metadata updates must also be coordinated with locks.
- MVCC central DBMS uses multiple versions of rows to remove row level
locking, and only requires any locking for metadata updates, such as
updating a btree or writing an update to the WAL (write ahead log).
- A lock is a lock from a conceptual point of view. How a lock is
implemented is a detail of the lock requirements.
I think we're mostly in agreement on all these points, other than the
relative complexity of MVCC against row level locking. I contest that MVCC
is conceptually simpler than row level locking, from the point of view of
deadlock detection and recovery, as well as increasing concurrency.
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
