与MSMQ和SQL Server的分布式事务，但有时会脏读 [英] Distributed transaction with MSMQ and SQL Server but sometimes getting dirty reads
问题描述
我们的SQL Server 2014数据库设置为READ_COMMITTED_SNAPSHOT.
Our SQL Server 2014 database is set to READ_COMMITTED_SNAPSHOT.
我们使用MSMQ和分布式事务(我们使用MassTransit 2.10)
We use MSMQ and distributed transactions (we use MassTransit 2.10)
在系统的一部分中，我们从队列中读取一条消息，进行数据库更新，然后将新消息发布到队列中(所有事务都在单个事务中完成).
In one part of our system we read a message from the queue, make database updates and then publish a new message to the queue (all under a single transaction).
我们发现一种情况，即使处理下一条消息(它从同一张表中读取第一部分更新)，似乎在处理下一条消息时也未提交更新，尽管我希望该消息仅在队列中的队列中.同时更新数据库.当我们稍后查询表时，更新的数据就如预期的那样.这似乎只有在我们的负载很高且很少的情况下才会发生.
We have found a situation where it seems that the update are not committed when the next message is processed (it reads from the same table the first part updates) even though I would expect that message to only be on the queue at the same time the database is updated. When we query the table later the updated data is there as expected. This only seems to happen when we have high load and very rarely.
我们的代码的简化版本
// code that processes message 1
using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions() { Timeout = TimeSpan.FromMinutes(30), IsolationLevel = IsolationLevel.ReadCommitted })
{
MethodThatUpdatesTableX();
MethodThatCreatesMessage2();
scope.Complete();
}
// message picked up from MSMQ and then (this runs in different thread):
// code that process message 2
using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions() { Timeout = TimeSpan.FromMinutes(30), IsolationLevel = IsolationLevel.ReadCommitted })
{
MethodThatReadsFromTableX(); // so here it seems that changes made in MethodThatUpdatesTableX is sometimes (though rarely) not read
// other stuff
}
这就是我的理解:
处置范围后，将提交对表X的更改以及已发布到队列的消息
When the scope is disposed the changes to table X is committed as well as the message published to the queue
从表XI读取MethodThatReadsFromTableX()时，将期望有更改(该会话不应在第一个会话完成之前创建，因为它无法从队列中提取消息)
When MethodThatReadsFromTableX() reads from table X I would expect the changes to be there (the session should not be created before the first one is completed because it wouldn’t be able to pick up the message from the queue)
我的期望正确吗?问题可能是什么?
Is my expectation correct? What could the issue be?
推荐答案
我会给你一个非常非常简短的答案.
I'll give you a really, really short answer.
使用Serializable作为隔离级别.这是保证读者会被作家封锁的唯一方法.
Use Serializable as your isolation level. It's the only way to guarantee that readers will be blocked by writers.
其余...
SQL非常擅长行级锁定，除非您要顺序插入集群索引中.在这种情况下，插入和处理重复的键错误会带来性能上的好处-数量级上的好处.
SQL is very good at row-level locking, unless you are inserting sequentially into a clustered index. In that case, there are performance benefits to inserting and handling the duplicate key error -- an order of magnitude benefit.
您还需要确保可能正在脏读的读者也正在使用可序列化的方式进行阅读-否则，您将遇到在提交数据库事务之前消耗已发布消息的情况.我已经在大批量生产系统中看到了这一点，并且这种情况确实发生了(当然，RabbitMQ不是MSMQ，RabbitMQ的调度要比MSMQ快得多).
You also need to make sure that your readers that might be getting dirty reads are also reading using serializable -- otherwise, you're going to have a case where a published message is being consumed before the database transaction is committed. I've seen this in high volume production systems, and it happens (of course, with RabbitMQ not MSMQ - RabbitMQ dispatches much quicker than MSMQ).
这篇关于与MSMQ和SQL Server的分布式事务，但有时会脏读的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
