QOF-to-SQL Proof of Concept

[Benjamin Carlyle]

On Sun, 2004-05-30 at 12:59, Derek Atkins wrote:
> Benjamin Carlyle <benjamincarlyle at optusnet.com.au> writes:
> > Whenever you say "BEGIN TRANSACTION;" a exclusive lock will be placed on
> > the whole database. When you commit or rollback your transaction the
> > lock is removed. Any insert, update, or other operation that modifies
> > the database will implicitly begin and commit a transaction during its
> > execution. Every select statement locks the whole database with a shared
> > lock and unlocks when the query is done.
> Good.  This is exactly what I expected.  So long as this works across
> multiple applications connecting to the same database, then it's just
> fine.  I think we'll still need to do some sort of internal locking
> to stop the race condition of having two apps trying to change the
> same piece of data..

> lock db
> do {
>    check status
>    if (status changed)
>       break;
>    update record
> } while (0);
> unlock db

> I'm not sure a BEGIN TRANSACTION / COMMIT TRANSACTION is sufficient to
> do this, unless we can actually perform a SELECT and get back real
> data in the middle of a transaction?

# Calculate update ahead of time, allowing user to
# enter new values.
BEGIN TRANSACTION;
SELECT version FROM mytable WHERE record = "record1";
# Programmatically compare result to the version we expected
# If different, cancel transaction and deal with error
# If same, proceed
UPDATE mytable SET foo = "bar", version = version+1 WHERE record =
"record1";
COMMIT;

I suspect other databases will require some kind of "lock whole
database" semantic. Sqlite won't, as the BEGIN TRANSACTION is sufficient
to exclusively lock the whole database. You're right, though. If you're
going to target more than just sqlite you will have to setup some larger
locking semantic. I believe most client-server databases will allow some
kind of "LOCK" command to be issued on specific tables, rows, or the
whole database.

Benjamin.