Re: [sqlite] Confirming locking and transaction characteristics
David Barrett <[EMAIL PROTECTED]> wrote: > Igor Tandetnik wrote: >> SQLite detects deadlock situation. The call trying to execute the >> update statement fails immediately with SQLITE_BUSY error. Neither >> transaction releases its locks: to make progress, one of them has to >> explicitly roll back. > > Aha, got it. One more question: take the same scenario as before, but > now have two distinct tables (barA and barB) inside the same database, Makes no difference: SQLite locks at file level, not table level. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confirming locking and transaction characteristics
Igor Tandetnik wrote: > David Barrett <[EMAIL PROTECTED]> wrote: >> There is a single table (bar) with a single column (foo) with a single >> row containing the integer value "1". Two processes (A and B) have >> opened the database in READWRITE mode. They both try to atomically >> increment the value at the same time. What happens? >> >> 1) [Process A] BEGIN TRANSACTION >> 2) [Process B] BEGIN TRANSACTION >> 3) [A] SELECT foo FROM bar >> (A acquires a SHARED lock) >> 4) [B] SELECT foo FROM bar >> (B acquires a SHARED lock) >> 5) [A] UPDATE bar SET foo=2 >> (A acquires the RESERVED lock) >> 6) [B] UPDATE bar SET foo=2 >> (? query fails, B's transaction aborted, SHARED lock released?) > > SQLite detects deadlock situation. The call trying to execute the update > statement fails immediately with SQLITE_BUSY error. Neither transaction > releases its locks: to make progress, one of them has to explicitly roll > back. Aha, got it. One more question: take the same scenario as before, but now have two distinct tables (barA and barB) inside the same database, and have each process only deal with its own table: 1) [Process A] BEGIN TRANSACTION 2) [Process B] BEGIN TRANSACTION 3) [A] SELECT foo FROM barA (A acquires a SHARED lock) 4) [B] SELECT foo FROM barB (B acquires a SHARED lock) 5) [A] UPDATE barA SET foo=2 (A acquires the RESERVED lock) 6) [B] UPDATE barB SET foo=2 (returns SQLITE_BUSY?) Would the UPDATE in (6) still return SQLITE_BUSY, and would one of the processes still need to rollback for the other to continue? I guess this is really a question of whether SQLite supports table locking or only database locking (and I'm guessing it's the latter, meaning the two disjoint tables should be put in different databases so they are independently locked). > Why not simply execute "UPDATE bar SET foo=foo+1;"? Heh, just looking for a simple example of a multi-query transaction. Thanks for your help! -david ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confirming locking and transaction characteristics
David Barrett <[EMAIL PROTECTED]> wrote: > There is a single table (bar) with a single column (foo) with a single > row containing the integer value "1". Two processes (A and B) have > opened the database in READWRITE mode. They both try to atomically > increment the value at the same time. What happens? > > 1) [Process A] BEGIN TRANSACTION > 2) [Process B] BEGIN TRANSACTION > 3) [A] SELECT foo FROM bar > (A acquires a SHARED lock) > 4) [B] SELECT foo FROM bar > (B acquires a SHARED lock) > 5) [A] UPDATE bar SET foo=2 > (A acquires the RESERVED lock) > 6) [B] UPDATE bar SET foo=2 > (? query fails, B's transaction aborted, SHARED lock released?) SQLite detects deadlock situation. The call trying to execute the update statement fails immediately with SQLITE_BUSY error. Neither transaction releases its locks: to make progress, one of them has to explicitly roll back. > 7) [A] COMMIT > (A gets the EXCLUSIVE lock, writes, clears the lock) Unless B rolled back after getting an error on step 6, A will get the same error here. Why not simply execute "UPDATE bar SET foo=foo+1;"? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Confirming locking and transaction characteristics
Can you please double-check the following to make sure it's accurate? I've read the "File Locking and Concurrency" page and I'm trying to verify how SQLite works in the following scenario: There is a single table (bar) with a single column (foo) with a single row containing the integer value "1". Two processes (A and B) have opened the database in READWRITE mode. They both try to atomically increment the value at the same time. What happens? 1) [Process A] BEGIN TRANSACTION 2) [Process B] BEGIN TRANSACTION 3) [A] SELECT foo FROM bar (A acquires a SHARED lock) 4) [B] SELECT foo FROM bar (B acquires a SHARED lock) 5) [A] UPDATE bar SET foo=2 (A acquires the RESERVED lock) 6) [B] UPDATE bar SET foo=2 (? query fails, B's transaction aborted, SHARED lock released?) 7) [A] COMMIT (A gets the EXCLUSIVE lock, writes, clears the lock) Is this right? In particular, in (6) does B's query fail because there is already a RESERVED lock held by A? Or is there some other system that notices B's conflicting transaction and aborts it? Or do I completely misunderstand this, and do both transactions succeed (leaving the value as "2" rather than "3")? Just trying to sort this all out in my head. Thanks! -david ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
