上期的问题只是冰山一角,我不是专业的数据库专家,我这里按我的知识理解来叙述。
我理解,事务隔离级别只是一种声明,数据库会依此自行分析sql语句,产生对应的锁来实现隔离效果。
生产环境的sql语句是由ORM框架生成的,这次的sql语句过于庞大和复杂,导致数据库产生的锁和我希望的锁不一致,进而在并发时仍旧产生问题。
下面开始模拟,在上一期的基础上,新增一张表:
--建测试表,trans_items是transactions的从表
CREATE TABLE [dbo].[trans_items](
[transaction_id] [int] NOT NULL,
[inserted_datetime] [datetime] NOT NULL
) ON [PRIMARY]
GO
然后重新定义一下插入数据的sql,为了理解得更好,这次模拟四个线程:
--模拟第一、三个服务的插入
Begin Transaction
Insert into transactions(insert_datetime) values(GETDATE())
--Insert into trans_items(transaction_id, inserted_datetime) values (SCOPE_IDENTITY(), GETDATE())
--Commit Transaction
--模拟第二个服务的插入
Begin Transaction
Insert into transactions(insert_datetime) values(GETDATE())
Insert into trans_items(transaction_id, inserted_datetime) values (SCOPE_IDENTITY(), GETDATE())
--Commit Transaction
--模拟第四个服务的插入
Begin Transaction
Insert into transactions(insert_datetime) values(GETDATE())
Insert into trans_items(transaction_id, inserted_datetime) values (SCOPE_IDENTITY(), GETDATE())
Commit Transaction
步骤号 | 执行步骤 | 说明 |
---|---|---|
1 | 运行第一个插入服务的脚本 | |
2 | 运行第二个插入服务的脚本 | |
3 | 运行每种测试的查询脚本 | |
4 | 运行第三个插入服务的脚本 | |
5 | 运行第四个插入服务的脚本 | 模拟了第四行记录比第一行记录先提交的情况 |
6 | 运行第二个插入服务的脚本中被注释的句子 | 模拟了第二行记录比第一行记录先提交的情况 |
7 | 运行第一个插入服务的脚本中被注释的句子 | |
8 | 运行第三个插入服务的脚本中被注释的句子 |
第一种测试:符合预期的查询语句
--这个写法的执行效果完全符合对隔离的预期
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SELECT * FROM transactions left join trans_items on transactions.id=trans_items.transaction_id
where exists (select 1 from trans_items where transactions.id=trans_items.transaction_id)
GO
查询完成时机:查询语句会一直等到第8步,也即所有插入语句都提交后,得到结果。
查询结果:能查到此轮插入的4行记录。这种隔离策略就像一个老好人,在打饭的过程中任由后来者插队。
第二种测试:
--不符合隔离预期的查询,区别在于"as alias"
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SELECT * FROM transactions left join trans_items on transactions.id=trans_items.transaction_id
where exists (select 1 from trans_items as alias where transactions.id=trans_items.transaction_id)
GO
查询完成时机:查询语句会在第6步得到结果,此时第一行、第三行记录的语句尚未提交。
查询结果:仅能查到此轮插入的第二、四行记录。如此一来,第一、三行又会被跳过了,不满足业务要求。
难道是隔离级别还不够吗?有兴趣的读者可以试试在继续保持“as alias”的情况下,将隔离级别进一步提高到SERIALIZABLE,结果也是不符合预期的。甚至,可能是我测试的方法不太对,似乎依照数据集大小、数据库内部缓存机制,隔离策略可能也会有变化,结果可以用混乱来形容!这样就只能去研究更底层的“锁”了。
首先,网上搜索可以知道用sys.dm_os_waiting_tasks、sys.dm_tran_locks两张表去分析当前被锁的任务和锁。利用这两张表分析得知,加上"as alias"后,可能是数据库解析索引失败,锁类型从keylock被换成了ridlock。寻找能显示指定加锁类型的方法,找到了sqlserver调优用的with语法:
微软官方学习文档,关于with关键字(学名表提示,Table hints)
with()方法的可传参数里,找到了INDEX(0)就可以“强制执行聚集索引扫描”,再搭配HOLDLOCK,就可以保持索引锁到事务结束。这样我们使用事务包裹,在ORM生成的sql之前先主动上锁,即使数据库引擎在解析sql后没有加上我们预期的锁也没关系了!
第三种测试:
--主动上锁,效果符合预期
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SELECT 1 FROM transactions with (INDEX(0), HOLDLOCK)
SELECT * FROM transactions left join trans_items on transactions.id=trans_items.transaction_id
where exists (select 1 from trans_items as alias where transactions.id=trans_items.transaction_id)
GO
查询完成时机:查询语句会一直等到第8步,也即所有插入语句都提交后,得到结果。
查询结果:能查到此轮插入的4行记录。和第一种测试的表现一致。
拓展内容,让我们再试试表锁:
第四种测试:
--主动上锁,但是表锁
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SELECT 1 FROM transactions with (TABLOCK, HOLDLOCK)
SELECT * FROM transactions left join trans_items on transactions.id=trans_items.transaction_id
where exists (select 1 from trans_items as alias where transactions.id=trans_items.transaction_id)
GO
查询完成时机:查询语句会在第7步得到结果,并且表锁会卡第三、四个脚本的提交,可见性能会略差。
查询结果:能查到此轮插入的第一、二行记录。也符合业务要求,上锁更激进,自己打到饭的时间更快!