SQL Server 事务:插入导致锁? [英] SQL Server transactions: insert causes locks?

问题描述

我遇到了以下问题:我在事务(默认 ADO.NET 事务)中向表(例如temp")插入一行.在提交该事务之前，从另一个会话(SQL Server 2008 管理工作室)运行的命令select * from temp"被阻止并且无法完成.

I'm having the following issue: I'm inserting a row to table (say, 'temp') inside a transaction (default ADO.NET transaction). Until that transaction is committed, the command 'select * from temp', running from another session (SQL Server 2008 management studio), is blocked and cannot be completed.

这对我来说似乎很奇怪.读取命令不应该返回所有先前的表行(除了新插入的行之外的所有行)并且不会被阻止吗?

This seems weird to me. Shouldn't the read command return all the previous table rows (everything but the newly inserted row) and not get blocked?

推荐答案

SQL Server 中的默认行为是获取锁，并且在提交事务之前不会释放这些锁.

The default behaviour in SQL Server is to take locks, and these will not be released until the transaction is committed.

有 3 种主要方法可以解决此问题 - READPAST 提示(这将跳过锁定的行)，阅读未提交 隔离级别(或 NOLOCK 提示)(将返回所有行，包括新插入的行，或 SNAPSHOT 隔离级别 - 但要让快照工作，这需要在服务器级别启用

There are 3 main ways around this - the READPAST hint (which will skip the locked row(s)), the READ UNCOMMITTED isolation level (or NOLOCK hint) (which will return all rows, including the newly inserted one(s), or the SNAPSHOT isolation level - but for snapshot to work, this needs to be enabled at the server level

这篇关于SQL Server 事务:插入导致锁?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！