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.
浙公網安備 33010602011771號