Ken <[EMAIL PROTECTED]> wrote: > > BEGIN TRANSACTION; > SELECT balance FROM accounts WHERE accountId = '123-45-6789'; > UPDATE accounts SET balance = <balance - withdrawal> > WHERE accountId = '123-45-6789'; > COMMIT; > > This is a comman and naive assumption that the balance selected > will remain consistent. >
Actually, SQLite does provide this guarantee. Nothing in the database will change during a transaction, except for changes caused by INSERT, UPDATE, and DELETE statements that occur within the transaction itself. It is not possible for another process to modify the value of the "balance" in between the SELECT and the UPDATE in the SQL above. This is true of SQLite because isolation in SQLite is "SERIALIZABLE". This is the highest level of isolate provided by SQL. Most client/server database engines by default implement "READ COMMITTED". The value of "balance" might change between the SELECT and the UPDATE in MySQL, for example. (I'm less clear about what happens in PostgreSQL and Oracle. The point is that your mileage may vary so be cautious.) But SQLite gets this right. Transactions are fully serializable, which means they appear as if the entire transaction happens instanteously with no chance for outside processes to change values in the middle of a transaction. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
