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
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2017-01-27 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
