Deadlock on Readcommited in Firebird

[sn00py]

HEllo i have a problem with a read commited Transaction on Firebird 3.0.7

using thsi script

Code: Select all

if not UniConnection1.Connected then
            UniConnection1.Connect;

        if UniTransaction1.Active then
            UniTransaction1.Commit;


        for i:=1 to 10000 do begin
            //UniTransaction1.IsolationLevel  := ilReadCommitted;
            UniTransaction1.StartTransaction;
            UNISQL1.SQL.Text    := 'UPDATE TEST SET TEST=6 WHERE ID = 1';
            UNISQL1.Execute;
            UniTransaction1.Commit;
        end;

If i start this app twice, i get randomly deadlocks.

If i create a SQL Script :

Code: Select all

COMMIT; SET TRANSACTION ISOLATION LEVEL READ COMMITTED WAIT;
UPDATE TEST SET TEST = 1 WHERE ID = 1; COMMIT; SET TRANSACTION ISOLATION LEVEL READ COMMITTED WAIT;
UPDATE TEST SET TEST = 1 WHERE ID = 1; COMMIT; SET TRANSACTION ISOLATION LEVEL READ COMMITTED WAIT;
UPDATE TEST SET TEST = 1 WHERE ID = 1; COMMIT; SET TRANSACTION ISOLATION LEVEL READ COMMITTED WAIT;
UPDATE TEST SET TEST = 1 WHERE ID = 1; COMMIT; SET TRANSACTION ISOLATION LEVEL READ COMMITTED WAIT;
* 10000 lines more of this*

and i start isql.exe twice, and use in both both "input test.sql"

both isql runs perfectly through the end.

What is the problem here?

[oleg0k]

Hello,
The error is caused by the server specifics rather than UniDAC functionality. We also get the same error using the standard Firebird utility isql.exe with your example. A "deadlock update conflicts with concurrent update" happens when multiple transaction want to modify the same row. Only one updater can really change the row and commit. As long as the first transaction hasn't committed, the update in the second transaction will wait (indefinitely or until the configured timeout). As soon as the first transaction commits, the update in the second transaction will end with this error (if instead the first transaction had rolled back, the second would have continued).

wbr, Oleg
Devart Team