Avoid deadlock on Firebird

Question

I would like to avoid DeadLock issue in code. I read the way is to use FOR UPDATE WITH LOCK also FbTransactionBehavior.Wait. But my second transaction, despite it is waiting for the first one to finish, always dies by DeadLock.

I wrote a very simple test to illustrate my issue, I pasted the test here below, easier than trying to explain with words (I guess)

The goal is to complete both transactions. The test result is a bit bizarre, the second transaction waits until the first one finishes but also dies. I expect both transactions completes (if it is possible)

tx1	tx2	Comments
tx starts	_	_
_	tx starts	_
Read id=6	_	_
Update id=6	_	_
_	Read id=6	<- tx2 Locked, it's ok, waiting for commit
Commit	_	<- tx2 unlocked
_	_	<- tx2 deadlock :(

using FirebirdSql.Data.FirebirdClient;

namespace fireb;

public class UnitTest1
{
    [Fact]
    public void Test1()
    {
        // tx 1 starts
        using var connection1 = new FbConnection("database=localhost:demo.fdb;user=sysdba;password=masterkey");
        connection1.Open();
        using var transaction1 = connection1.BeginTransaction(
            new FbTransactionOptions() { TransactionBehavior = FbTransactionBehavior.Wait, WaitTimeout = TimeSpan.FromSeconds(5) });

        // tx 2 starts
        using var connection2 = new FbConnection("database=localhost:demo.fdb;user=sysdba;password=masterkey");
        connection2.Open();
        using var transaction2 = connection2.BeginTransaction(
            new FbTransactionOptions() { TransactionBehavior = FbTransactionBehavior.Wait, WaitTimeout = TimeSpan.FromSeconds(30) });

        // updating on tx1
        using var command_pre1 = new FbCommand("select * from demo where id = 6 FOR UPDATE WITH LOCK;", connection1, transaction1);
        command_pre1.ExecuteReader().Read();
        using var command1 = new FbCommand("update demo set foobar = 'pp1' where id = 6;", connection1, transaction1);
        command1.ExecuteNonQuery();

        // commit in 10''
        Action myCommit = () => {
            transaction1.Commit();
            transaction1.Dispose();
            command_pre1.Dispose();
            command1.Dispose();
            connection1.Close();
            connection1.Dispose();
        };

        var aTimer = new System.Timers.Timer(10000);
        aTimer.Elapsed += (a,b) => myCommit();
        aTimer.AutoReset = false;
        aTimer.Enabled = true;        

        // updating tx2
        using var command_pre2 = new FbCommand("select * from demo where id = 6 FOR UPDATE WITH LOCK;", connection2, transaction2);
        command_pre2.ExecuteReader().Read();  // <--- here dies. How to avoid? 
        using var command2 = new FbCommand("update demo set foobar = 'pp2' where id = 6;", connection2, transaction2);
        command2.ExecuteNonQuery();

        // Commit tx 2
        transaction2.Commit();

    }

This is the error:

FirebirdSql.Data.FirebirdClient.FbException : deadlock update conflicts with concurrent update concurrent transaction number is 67 ---- FirebirdSql.Data.Common.IscException : deadlock update conflicts with concurrent update

End of question

---

If you want to reproduce:

To create the table ...

    // from here just arranging database and data

    public UnitTest1()
    {
        // Just to create database, table and row
        // CreaBd();
        // CreaTaula();
        // InsertData();
    }

    private static void CreaTaula()
    {
        using var connection = new FbConnection("database=localhost:demo.fdb;user=sysdba;password=masterkey");
        connection.Open();
        using var transaction = connection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
        using var command = new FbCommand(
            "create table demo (id int primary key, foobar varchar(20) character set utf8); ", 
            connection, transaction);
        command.ExecuteNonQuery();
        transaction.Commit();
    }

    private static void InsertData()
    {
        using var connection = new FbConnection("database=localhost:demo.fdb;user=sysdba;password=masterkey");
        connection.Open();
        using var transaction = connection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
        using var command = new FbCommand(
            "insert into demo values (6, 'FooBar');", 
            connection, transaction);
        command.ExecuteNonQuery();
        transaction.Commit();
    }

    private static void CreaBd()
    {
        FbConnectionStringBuilder builder = new FbConnectionStringBuilder();
        builder.DataSource = "localhost";
        builder.UserID = "sysdba";
        builder.Password = "masterkey";
        builder.Database = "demo.fdb";
        builder.ServerType = FbServerType.Default;

        FbConnection.CreateDatabase(builder.ConnectionString);
    }
}

If you want to run firebird in docker

docker run -d \ 
    --name firebird \
    -p 3050:3050 \
    -e "ISC_PASSWORD=masterkey" \
    -v /Users/dani/tmp/fireb/dockerdb:/databases \
    jacobalberty/firebird