SQLite事務與自增深度分析

SQLite什麼都好，就怕“database is locked”這些年來想盡辦法去規避它。

測試代碼：

static void Test2()
{
    XCode.Setting.Current.TransactionDebug = true;

    XTrace.WriteLine(Role.Meta.Count + "");
    XTrace.WriteLine(Log.Meta.Count + "");
    Console.Clear();

    Task.Run(() => TestTask(1));
    Thread.Sleep(1000);
    Task.Run(() => TestTask(2));
}

static void TestTask(Int32 tid)
{
    try
    {
        XTrace.WriteLine("TestTask {0} Start", tid);
        using (var tran = Role.Meta.CreateTrans())
        {
            var role = new Role();
            role.Name = "R" + DateTime.Now.Millisecond;
            role.Save();
            XTrace.WriteLine("role.ID={0}", role.ID);

            Thread.Sleep(3000);

            role = new Role();
            role.Name = "R" + DateTime.Now.Millisecond;
            role.Save();
            XTrace.WriteLine("role.ID={0}", role.ID);

            Thread.Sleep(3000);

            if (tid == 2) tran.Commit();
        }
    }
    catch (Exception ex)
    {
        XTrace.WriteException(ex);
    }
    finally
    {
        XTrace.WriteLine("TestTask {0} End", tid);
    }
}

預熱環境以後，我們開了兩個任務去執行測試函數，間隔1秒。
測試函數負責插入兩行數據，間隔3秒。
第一個任務最後會回滾，第二個任務提交。
顯然，兩個任務會重疊。

比較好奇，任務1申請得到自增1後，任務2申請得到的自增會是多少？
任務1回滾以後，它所申請得到的自增數字如何處理？


結果：

02:45:03.470  6 Y 5 TestTask 1 Start
02:45:03.470  6 Y 5 Transaction.Begin ReadCommitted
02:45:03.486  6 Y 5 Select Count(*) From Role Where Name='R470'
02:45:03.501  6 Y 5 Insert Into Role(Name, IsSystem, Permission) Values('R470', 0, '');Select last_insert_rowid() newid
02:45:03.517  6 Y 5 開始初始化實體類UserX
02:45:03.517  6 Y 5 完成初始化實體類UserX
02:45:03.533  6 Y 5 role.ID=11
02:45:04.486 14 Y 6 TestTask 2 Start
02:45:04.486 14 Y 6 Transaction.Begin ReadCommitted
02:45:04.486 14 Y 6 Select Count(*) From Role Where Name='R486'
02:45:04.486 14 Y 6 Insert Into Role(Name, IsSystem, Permission) Values('R486', 0, '');Select last_insert_rowid() newid
02:45:05.251 15 Y 7 Transaction.Begin ReadCommitted
02:45:05.251 15 Y 7 Insert Into Log(Category, [Action], LinkID, CreateUserID, CreateTime, Remark) Values('角色', '添加', 11, 0, '2017-01-27 02:45:03', 'ID=11,Name=R470');Select last_insert_rowid() newid
02:45:06.548  6 Y 5 Select Count(*) From Role Where Name='R548'
02:45:06.548  6 Y 5 Insert Into Role(Name, IsSystem, Permission) Values('R548', 0, '');Select last_insert_rowid() newid
02:45:06.548  6 Y 5 role.ID=12
02:45:09.555  6 Y 5 Transaction.Rollback ReadCommitted
02:45:09.555  6 Y 5 TestTask 1 End
02:45:09.618 14 Y 6 SQL耗時較長，建議優化 5,120毫秒 Insert Into Role(Name, IsSystem, Permission) Values('R486', 0, '');Select last_insert_rowid() newid
02:45:09.618 14 Y 6 role.ID=11
02:45:12.633 14 Y 6 Select Count(*) From Role Where Name='R633'
02:45:12.633 14 Y 6 Insert Into Role(Name, IsSystem, Permission) Values('R633', 0, '');Select last_insert_rowid() newid
02:45:12.633 14 Y 6 role.ID=12
02:45:15.649 14 Y 6 Transaction.Commit ReadCommitted
02:45:15.649 14 Y 6 TestTask 2 End
02:45:15.774 15 Y 7 SQL耗時較長，建議優化 10,519毫秒 Insert Into Log(Category, [Action], LinkID, CreateUserID, CreateTime, Remark) Values('角色', '添加', 11, 0, '2017-01-27 02:45:03', 'ID=11,Name=R470');Select last_insert_rowid() newid
02:45:15.774 15 Y 7 Transaction.Commit ReadCommitted
02:45:16.622 16 Y 9 Transaction.Begin ReadCommitted
02:45:16.622 16 Y 9 Insert Into Log(Category, [Action], LinkID, CreateUserID, CreateTime, Remark) Values('角色', '添加', 12, 0, '2017-01-27 02:45:06', 'ID=12,Name=R548');Select last_insert_rowid() newid
02:45:16.622 16 Y 9 Insert Into Log(Category, [Action], LinkID, CreateUserID, CreateTime, Remark) Values('角色', '添加', 11, 0, '2017-01-27 02:45:09', 'ID=11,Name=R486');Select last_insert_rowid() newid
02:45:16.622 16 Y 9 Insert Into Log(Category, [Action], LinkID, CreateUserID, CreateTime, Remark) Values('角色', '添加', 12, 0, '2017-01-27 02:45:12', 'ID=12,Name=R633');Select last_insert_rowid() newid
02:45:16.637 16 Y 9 Transaction.Commit ReadCommitted

從測試結果來看：
1，任務1申請得到11和12，任務2也是
2，任務1申請得到11後，任務2啟動，執行到Insert時阻塞了5.12秒，直到任務1回滾了事務
3，線程15和16是非同步寫日誌，顯然它們也被阻塞，線程15阻塞10.519秒，知道任務2提交事務


結論：SQLite執行更新事務操作時使用排它鎖，強制自增數字同步分配！


參考：
http://sqlite.1065341.n5.nabble.com/Transactions-and-sqlite3-last-insert-rowid-td8905.html

 

> If I understand it correctly, connection C1 can do an INSERT, get
> ROWID 4, C2 does an INSERT, gets 5, and commits, and then C1 commits,
> with its 4; if C1 rolled back, there's no 4 in the database, just 5
> and whatever else, correct?
>
No, this can't happen. As soon as C1 does its insert, it acquires an
exclusive lock on the database. C2 can't do an insert until C1 either
commits or rolls back and releases the lock. If C1 committed, then C2
will get 5, if C1 rolled back, then C2 will get 4.