SQL事务隔离级别的问题后续

上期问题链接

上期的问题只是冰山一角，我不是专业的数据库专家，我这里按我的知识理解来叙述。

我理解，事务隔离级别只是一种声明，数据库会依此自行分析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步得到结果，并且表锁会卡第三、四个脚本的提交，可见性能会略差。

查询结果：能查到此轮插入的第一、二行记录。也符合业务要求，上锁更激进，自己打到饭的时间更快！