Hello,
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?
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.
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.
Martin
Am 11.05.2011 17:24, schrieb Dagdamor:
> and two transactions (from two different connections) are trying to insert a
> record at once:
>
> 00:01 Transaction A: BEGIN
> 00:02 Transaction B: BEGIN
> 00:03 Transaction A: INSERT INTO test VALUES (1) // works okay
> 00:04 Transaction B: INSERT INTO test VALUES (1) // aborts with 'duplicate
> key' error! why???
> 00:05 Transaction A: ROLLBACK // works okay, table remains empty
> 00:06 Transaction B: ??? // has nothing to do because was unable to insert a
> record into an empty table!
>
> To put it simple, transaction A tried to insert a record but soon aborted
> itself via ROLLBACK. If I understand transactions principle correctly, a
> rolled-back transaction should act like it never happened in the first place,
> and other threads should not see its traces. But for some reason another
> transaction noticed that and refused to insert values into table. The
> question is: is that a correct behavior, and I should keep this in mind, or
> SQLite would handle this scenario different way? :/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
