On Wed, May 11, 2011 at 12:18 PM, Martin Engelschalk
<engelsch...@codeswift.com> wrote:
> This question does not arise with SQLite, because parallel transaction
> are not supported, as Igor and Pavel pointed out.
>
> However, consider this: If you have a unique constraint on a table like
> in your example, when should the database enforce it?
SQLite enforces UNIQUE constraints (and indices) immediately. Foreign
key reference enforcement can be delayed to the end of the
transaction, but UNIQUE constraints, sadly, cannot be.
> To use your example and add a second colum
>
> 00:01 Transaction A: BEGIN
> 00:02 Transaction B: BEGIN
> 00:03 Transaction A: INSERT INTO test VALUES (1, 'foo') // works okay
> 00:04 Transaction B: INSERT INTO test VALUES (1, 'bar') // lets say, this
> also works like you expected.
With SQLite3 B will have to wait for A to finish or rollback its transaction.
> 00:05 Transaction B: COMMIT // shall this work? If yes, the Record ('1',
> 'bar') is now committed. However, Transaction A was first!
> 00:06 Transaction A: COMMIT // This cannot work. What error message would you
> expect?
>
> Now, consider large transactions with many Operations.
> Therefore, the second insert fails on every database system i ever
> encountered.
B will have constructed the key before it knew that A had created a
row with that same key. This necessarily means that B's insert must
fail (unless B used INSERT OR REPLACE, say). And this is necessarily
true for any RDBMS that gives you unique constraints and/or indices.
What can vary is when the constraint is checked: at insert or commit
time (see above).
Nico
--
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
