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 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 -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
