[From nobody Wed Sep 10 23:57:46 2003
Message-ID: <3F60009E.70507@optusnet.com.au>
Date: Thu, 11 Sep 2003 14:57:02 +1000
From: Benjamin <benjamincarlyle@optusnet.com.au>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.4) Gecko/20030827 Debian/1.4-3
X-Accept-Language: en
MIME-Version: 1.0
To: Matthew Vanecek <mevanecek@yahoo.com>
Subject: Re: SQLite and transactional security
References: <20030910143408.18767.qmail@dandelion-patch.mit.edu>	<sjm4qzkbs1l.fsf@kikki.mit.edu>
	<1063241435.25313.1.camel@reliant.home.pri>
In-Reply-To: <1063241435.25313.1.camel@reliant.home.pri>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Matthew Vanecek wrote:
> drh (the author of sqlite) wrote:
> >To put it another way, updates to an SQLite database are
> >atomic.  Either the entire update occurs or none of it
> >occurs.  You never run into a situation where a program
> >crash causes an update to occur half-way.

> That's a wonderful thing to know.  A question, though: does SQLite
> support transactional updates (e.g., multiple updates rolled into one
> BEGIN/COMMIT block)?

sqlite supports transactions based on the BEGIN/END TRANSACTION syntax. 
If a transaction is not open when the database is modified one is 
started for it, ie:

INSERT INTO ...;
UPDATE ...;

is equivalent to

BEGIN TRANSACTION;
INSERT INTO ...;
END TRANSACTION;
BEGIN TRANSACTION;
UPDATE ...;
END TRANSACTION;

while no implicit transactions are opened in this example, only the 
explicit one.

BEGIN TRANSACTION;
INSERT INTO ...;
UPDATE ...;
END TRANSACTION;

The atomic rollback mechanism is based on transactions, not individual 
update commands or individual calls to sqlite_exec(). If a rollback is 
requested after a BEGIN TRANSACTION the database will be rolled back to 
the state it was in at the beginning of the transaction. The logging 
mechanism ensures that any program or machine crash will be treated as a 
rollback. The database is always safe. As with other databases (well, 
databases other than mySQL ;) It will never be corrupted unless there 
are bugs in the code.

Sqlite does not support nested transactions. Note also that beginning a 
transaction write-locks the entire database until the transaction is 
committed or rolled back, just as executing a SELECT read-locks the 
entire database.

It's interesting to see drh showing an interesting in gnucash :) He's a 
good bloke and very efficient at resolving bugs or deficiencies in the 
sqlite code-base.

Benjamin.

]