SQL Server - [SELECT] 锁定 [UPDATE] 吗?

问题描述

我有一个复杂的选择查询和一个巨大的表.

I have a complex select query and a huge table.

我正在运行这个 select 语句，同时一个 Update 语句到达并尝试更新表.

I'm running this select statement, meanwhile an Update statement arrives and tries to update the table.

恕我直言 - 更新需要一个独占锁 - 所以更新语句必须等待直到选择命令完成.

IMHO - update requires an exclusive lock - so the update statement will have to wait till the select command is finished.

1. 我说得对吗?

1. Am I right ?

我该怎么做才能:执行复杂的select，和还让update命令运行(目前我不关心脏数据)

what can I do in order to: execute the complex select, and also let the update command run (currently I don't care about dirty data)

推荐答案

是的 - 在一定程度上.

Yes - to a degree.

SELECT 持有共享锁的时间取决于事务的隔离级别:

How long a SELECT holds on to a shared lock is depending on the isolation level of the transaction:

• READ UNCOMMITTED - 根本没有获取共享锁 - UPDATE 没有被阻止
• READ COMMITTED - 仅在读取数据期间获取共享锁 - UPDATE 可能会被阻塞很短的时间
• REPEATABLE READ 和 SERIALIZABLE - 获取共享锁并保持到事务结束 - UPDATE 被阻塞，直到 SELECT 交易结束

• READ UNCOMMITTED - no shared lock is acquired at all - UPDATE is not blocked
• READ COMMITTED - shared lock is acquired just for the duration of reading the data - UPDATE might be blocked for a very short period of time
• REPEATABLE READ and SERIALIZABLE - shared lock is acquired and held on to until the end of the transaction - UPDATE is blocked until the SELECT transaction ends

从技术上讲，UPDATE 语句首先获得一个 UPDATE 锁——它与共享锁兼容(由 SELECT 使用)——在它读取要更新的行的当前值的持续时间内.

Technically, the UPDATE statement first gets an UPDATE lock - which is compatible with a shared lock (as used by the SELECT) - for the duration of the time while it's reading the current values of the rows to be updated.

一旦完成，Update 锁将升级为独占锁，以便将新数据写入表中.

Once that's done, the Update lock is escalated to an exclusive lock for the new data to be written to the table.

这篇关于SQL Server - [SELECT] 锁定 [UPDATE] 吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！