Firebird避免死锁：事务等待提交后仍触发死锁的问题求助

我需要避免Firebird代码中的死锁问题，尝试使用FOR UPDATE WITH LOCK和FbTransactionBehavior.Wait配置事务等待，但第二个事务在等待第一个事务提交完成后，仍触发死锁/更新冲突，希望让两个事务都能正常执行完成。

复现流程 #

测试代码 #

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);
        using var reader1 = command_pre1.ExecuteReader();
        reader1.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();  // <--- 此处触发死锁/更新冲突，如何避免？
        using var command2 = new FbCommand("update demo set foobar = 'pp2' where id = 6;", connection2, transaction2);
        command2.ExecuteNonQuery();

        // Commit tx 2
        transaction2.Commit();
    }
}

报错信息 #

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

复现环境准备 #

创建数据库、表及测试数据 #

// 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);
}

Docker启动Firebird服务 #

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

解决方案 #

问题根源 #

你遇到的并非真正的死锁，而是Firebird的更新冲突（update conflict）。核心原因是事务使用了RepeatableRead隔离级别：该级别要求事务全程使用启动时的快照，当其他事务修改并提交目标行后，当前事务的快照已过时，尝试获取锁并操作时会触发冲突。

具体解决步骤 #

1. 调整事务隔离级别为ReadCommitted
   ReadCommitted是Firebird的默认隔离级别，允许事务读取其他事务已提交的最新数据，避免快照过时导致的冲突。修改事务启动代码，显式指定隔离级别：

   // tx1启动事务时指定ReadCommitted
   using var transaction1 = connection1.BeginTransaction(
       new FbTransactionOptions() { 
           IsolationLevel = System.Data.IsolationLevel.ReadCommitted,
           TransactionBehavior = FbTransactionBehavior.Wait, 
           WaitTimeout = TimeSpan.FromSeconds(5) 
       });
   
   // tx2同样指定
   using var transaction2 = connection2.BeginTransaction(
       new FbTransactionOptions() { 
           IsolationLevel = System.Data.IsolationLevel.ReadCommitted,
           TransactionBehavior = FbTransactionBehavior.Wait, 
           WaitTimeout = TimeSpan.FromSeconds(30) 
       });
   

2. 规范释放数据库资源
   确保ExecuteReader()返回的FbDataReader被正确释放，避免不必要的锁持有时间延长：

   using var reader2 = command_pre2.ExecuteReader();
   reader2.Read();
   

3. 优化事务逻辑
   尽量缩短事务的执行时间，避免长时间持有锁；如果业务允许，也可以考虑使用FOR UPDATE NOWAIT或SKIP LOCKED跳过等待，但这只适用于不需要等待锁的场景。

内容的提问来源于stack exchange，提问作者dani herrera