> Therefore, the second insert fails on every database system i ever
> encountered.
Apparently you didn't encounter Oracle. In such situation Oracle
freezes transaction B until transaction A is committed or rollbacked.
After that it knows what to return to transaction B - error or success
correspondingly.
Pavel
On Wed, May 11, 2011 at 1:18 PM, Martin Engelschalk
<engelsch...@codeswift.com> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
