SQL Server-[SELECT]会锁定[UPDATE]吗? [英] SQL Server - does [SELECT] lock [UPDATE]?

问题描述

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

I have a complex select query and a huge table.

我正在运行此select语句，同时一条Update语句到达并尝试 update 该表.

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

恕我直言-更新需要排他锁-因此update语句将必须 wait 直到select命令完成.

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)

推荐答案

是-在一定程度上.

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]吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！