Igor Tandetnik wrote: > On 2/3/2014 1:07 PM, Baruch Burstein wrote: >> 1) How does a transaction affect SELECTs? If I start a transaction and do >> an UPDATE/DELETE/INSERT, what data will a SELECT in the same transaction >> see? > > The new data. A transaction always sees its own changes. > >> What about a SELECT in a different connection? > > Depends. In journal mode, the reader transaction is blocked while a > writer is in progress; you won't be able to run that SELECT statement
Erm, wrong. [1] sqlite> create table t (i); [1] sqlite> insert into t values (1); [1] sqlite> begin; [1] sqlite> insert into t values (2); [2] sqlite> begin; [2] sqlite> /* as you see, there are no problem in starting transaction concurrently with pending update in [1]: */ [2] sqlite> select * from t; 1 [2] sqlite> /* as you see, SELECT is also successful and returns unmodified data: */ There are *different* restriction in rollback journal mode: you cannot *COMMIT* in [1] while transaction in [2] is still active. [1] sqlite> COMMIT; Error: database is locked [1] sqlite> [2] sqlite> COMMIT; /* or ROLLBACK; */ [1] sqlite> COMMIT; [1] sqlite> /* succeed this time */ In WAL mode, there are no such restriction. (You *can* take any number of SHARED locks when one of transactions already got RESERVED lock. You cannot upgrade that SHARED lock to RESERVED [required for any db modification] if other transaction already got RESERVED lock; you cannot upgrade RESERVED lock to EXCLUSIVE lock while there are other transaction(s) that acquired SHARED lock [required for COMMIT in rollback journal mode, not required in WAL mode]) > until the writer transaction commits (in which case SELECT will see new > data) or rolls back (in which case it will see old data). > > In WAL mode, a reader is allowed to read while a writer transaction is > in progress. It reads the old data, before any changes. > >> 2) Can 2 connections run 'BEGIN;' ? > > Yes. They are presumed to be readers, until they attempt to run a data > modification statement. Two readers can happily co-exist. >> If so, who gets the final say on the data? The first to do the >> 'COMMIT;'? What will happen when the other does a >> COMMIT? > > When these two transactions issue a BEGIN statement, each acquires a > shared lock. The first one to run UPDATE or similar gets a reserved > lock, which basically says "I'm going to write at some later time; for > now, I'm waiting for all existing readers to clear". If the second > transaction tries to run UPDATE, it will get an error when trying to > obtain its own reserved lock. At this point, the system is in a deadlock > - no progress can be made until one of the transactions rolls back. > > To avoid this situation, use BEGIN IMMEDIATE or BEGIN EXCLUSIVE for > transactions that you know will be making changes (not just reading). > > For details, see > > http://www.sqlite.org/lockingv3.html > http://www.sqlite.org/atomiccommit.html > http://www.sqlite.org/lang_transaction.html _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
