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
