- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
给定一个需要 5 分钟左右的 UPDATE 执行,当 SELECT 尝试从同一个表中检索数据时会发生什么?对于不同的事务隔离级别和 SELECT WITH (NOLOCK)
,SELECT 是否等待 UPDATE?如果没有,SELECT 是否返回旧数据(UPDATE 之前的数据)或当前插入记录的一部分(例如当前插入记录的 50%)?
如果发现以下问题,但它仅描述在长 SELECT 期间执行和 UPDATE 时会发生什么。
最佳答案
This post by Gavin Draper explains it quite well and contains some example query's.
SQL Server Isolation Levels By Example
Isolation levels in SQL Server control the way locking works between transactions.
SQL Server 2008 supports the following isolation levels
- Read Uncommitted
- Read Committed (The default)
- Repeatable Read
- Serializable
- Snapshot
Before I run through each of these in detail you may want to create a new database to run the examples, run the following script on the new database to create the sample data. Note : You’ll also want to drop the IsolationTests table and re-run this script before each example to reset the data.
CREATE TABLE IsolationTests
(
Id INT IDENTITY,
Col1 INT,
Col2 INT,
Col3 INTupdate te
)
INSERT INTO IsolationTests(Col1,Col2,Col3)
SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3Also before we go any further it is important to understand these two terms….
- Dirty Reads – This is when you read uncommitted data, when doing this there is no guarantee that data read will ever be committed meaning the data could well be bad.
- Phantom Reads – This is when data that you are working with has been changed by another transaction since you first read it in. This means subsequent reads of this data in the same transaction could well be different.
Read Uncommitted
This is the lowest isolation level there is. Read uncommitted causes no shared locks to be requested which allows you to read data that is currently being modified in other transactions. It also allows other transactions to modify data that you are reading.
As you can probably imagine this can cause some unexpected results in a variety of different ways. For example data returned by the select could be in a half way state if an update was running in another transaction causing some of your rows to come back with the updated values and some not to.
To see read uncommitted in action lets run Query1 in one tab of Management Studio and then quickly run Query2 in another tab before Query1 completes.
Query1
BEGIN TRAN
UPDATE IsolationTests SET Col1 = 2
--Simulate having some intensive processing here with a wait
WAITFOR DELAY '00:00:10'
ROLLBACKQuery2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM IsolationTestsNotice that Query2 will not wait for Query1 to finish, also more importantly Query2 returns dirty data. Remember Query1 rolls back all its changes however Query2 has returned the data anyway, this is because it didn't wait for all the other transactions with exclusive locks on this data it just returned what was there at the time.
There is a syntactic shortcut for querying data using the read uncommitted isolation level by using the
NOLOCK
table hint. You could change the above Query2 to look like this and it would do the exact same thing.SELECT * FROM IsolationTests WITH(NOLOCK)
Read Committed
This is the default isolation level and means selects will only return committed data. Select statements will issue shared lock requests against data you’re querying this causes you to wait if another transaction already has an exclusive lock on that data. Once you have your shared lock any other transactions trying to modify that data will request an exclusive lock and be made to wait until your Read Committed transaction finishes.
You can see an example of a read transaction waiting for a modify transaction to complete before returning the data by running the following Queries in separate tabs as you did with Read Uncommitted.
Query1
BEGIN TRAN
UPDATE Tests SET Col1 = 2
--Simulate having some intensive processing here with a wait
WAITFOR DELAY '00:00:10'
ROLLBACKQuery2
SELECT * FROM IsolationTests
Notice how Query2 waited for the first transaction to complete before returning and also how the data returned is the data we started off with as Query1 did a rollback. The reason no isolation level was specified is because Read Committed is the default isolation level for SQL Server. If you want to check what isolation level you are running under you can run
DBCC useroptions
. Remember isolation levels are Connection/Transaction specific so different queries on the same database are often run under different isolation levels.Repeatable Read
This is similar to Read Committed but with the additional guarantee that if you issue the same select twice in a transaction you will get the same results both times. It does this by holding on to the shared locks it obtains on the records it reads until the end of the transaction, This means any transactions that try to modify these records are forced to wait for the read transaction to complete.
As before run Query1 then while its running run Query2
Query1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM IsolationTests
WAITFOR DELAY '00:00:10'
SELECT * FROM IsolationTests
ROLLBACKQuery2
UPDATE IsolationTests SET Col1 = -1
Notice that Query1 returns the same data for both selects even though you ran a query to modify the data before the second select ran. This is because the Update query was forced to wait for Query1 to finish due to the exclusive locks that were opened as you specified Repeatable Read.
If you rerun the above Queries but change Query1 to Read Committed you will notice the two selects return different data and that Query2 does not wait for Query1 to finish.
One last thing to know about Repeatable Read is that the data can change between 2 queries if more records are added. Repeatable Read guarantees records queried by a previous select will not be changed or deleted, it does not stop new records being inserted so it is still very possible to get Phantom Reads at this isolation level.
Serializable
This isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads. It does this by placing range locks on the queried data. This causes any other transactions trying to modify or insert data touched on by this transaction to wait until it has finished.
You know the drill by now run these queries side by side…
Query1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM IsolationTests
WAITFOR DELAY '00:00:10'
SELECT * FROM IsolationTests
ROLLBACKQuery2
INSERT INTO IsolationTests(Col1,Col2,Col3)
VALUES (100,100,100)You’ll see that the insert in Query2 waits for Query1 to complete before it runs eradicating the chance of a phantom read. If you change the isolation level in Query1 to repeatable read, you’ll see the insert no longer gets blocked and the two select statements in Query1 return a different amount of rows.
Snapshot
This provides the same guarantees as serializable. So what's the difference? Well it’s more in the way it works, using snapshot doesn't block other queries from inserting or updating the data touched by the snapshot transaction. Instead row versioning is used so when data is changed the old version is kept in tempdb so existing transactions will see the version without the change. When all transactions that started before the changes are complete the previous row version is removed from tempdb. This means that even if another transaction has made changes you will always get the same results as you did the first time in that transaction.
So on the plus side your not blocking anyone else from modifying the data whilst you run your transaction but…. You’re using extra resources on the SQL Server to hold multiple versions of your changes.
To use the snapshot isolation level you need to enable it on the database by running the following command
ALTER DATABASE IsolationTests
SET ALLOW_SNAPSHOT_ISOLATION ONIf you rerun the examples from serializable but change the isolation level to snapshot you will notice that you still get the same data returned but Query2 no longer waits for Query1 to complete.
Summary
You should now have a good idea how each of the different isolation levels work. You can see how the higher the level you use the less concurrency you are offering and the more blocking you bring to the table. You should always try to use the lowest isolation level you can which is usually read committed.
关于sql - 在冗长的 UPDATE 期间进行 SELECT - 不同事务隔离级别的 SELECT 和 SELECT WITH (NOLOCK) 会发生什么?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/24942441/
我的公司有一个 Web 应用程序,其中包含纯 JavaScript,它以自己的方式使用 $ -“美元符号”,如下所示: function $(e) { return document.getE
doc说“这意味着对单个节点上单个分区内的行的写入仅对执行操作的客户端可见”。 如果有另一个 client2 在同一个分区和同一个节点上执行操作,那么文档中提到的“THE CLIENT”执行的写入是否
只是一个想法,但在 DIV 上使用 IFRAME 本质上会使该元素与窗口隔离,从而降低 IFRAME 中运行的脚本速度 不会影响其他框架/窗口吗? 最佳答案 是的,对于第一部分,iframe 会“某种
我有以下模型 Inventory [product_name, quantity, reserved_quantity] 有数据 [Shirt, 1, 0] [Shorts, 10, 0] 如果以下代
我面临的情况如下。因为ThreadPool是每个进程1个实例,所以我的问题是是否会在 3秒后取消方法2排队的任务? http request comes in *method 1 gets execu
我希望在 Dart 中创建一个 Isolate,我可以通过编程方式暂停和恢复。这是我使用的代码。 import 'dart:io'; import 'dart:isolate'; void main(
我想编写一个具有隔离作用域的指令,但也希望使该作用域可用于父作用域的 Controller 。我找到了这个解决方案: app.directive('popupbutton', [functi
我有一个像这样的 JSON 对象: [ {"Subject": "Physics", "Active": 48, "Date": "2020-01-22T00:00:00Z"}, {"Su
我正在使用 Elixir 自动执行用 Gherkin 编写的规范中定义的验收测试。一种方法是使用名为 Cabbage 的 ExUnit 插件。 . 现在,ExUnit 似乎提供了一个在任何单个测试之前
我被要求为多个用户配置一个带有 docker 的 ubuntu 18.04 服务器。 目的: 我们有多个编写测试用例的测试人员。但是我们的笔记本电脑速度不够快,无法在 docker 环境中构建项目和运
我一直在网上寻找完整的解决方案,但到目前为止,我只能找到不合适的部分。 我正在寻找一个可以查看图像文件、循环遍历文件并隔离 Sprite 然后保存它们的程序。之后,我需要一种方法来重新访问该 Spri
我想知道如何隔离 JavaScript 函数的执行以避免浏览器崩溃。 示例:如果我想在控制台中输出一个包含大约 10k 元素的关联数组,浏览器将停止响应。我怎样才能避免这种情况? 最佳答案 解决方案是
我必须向我的数据库添加大量信息。添加此信息大约需要 5-7 分钟。我需要添加交易。 我试过这个: try { db.Connection.Open(); db.Transaction
我有 6 个 iframe,它们来自同一个域,但具有不同的 url 和子目录。他们都使用 html header “set-cookie”设置了一个名称相同但值不同的 cookie。我需要将它们的 c
我正在编写一个代码,它基本上读取一个文本文件(表格格式)并检查该文件是否包含预期的数据类型。为此我写了下面的课。 示例文件应该是这样的。 name age abc 20 xyz
我有一个表,线程。我有一个表,thread_participants。我正在尝试隔离与特定 thread_id 和特定 thread_participants.user_id 标识的行。 例如,如果
我有一个非常实际的问题。我的数据库中有大约 400 篇文章,在这些文章中我有其他文章的链接。在转换过程中,链接被破坏。我们在 CMS 中手动插入新的菜单链接项。我想制作一个脚本来查找(文章)id 并将
关闭。这个问题是off-topic .它目前不接受答案。 想改进这个问题吗? Update the question所以它是on-topic用于堆栈溢出。 关闭 10 年前。 Improve thi
我在想是否可以在 postgres 数据库(高于 8.3 的版本)上创建一个只能访问特定指定模式的用户。问题是,在我的数据库中我有一些模式。如果我撤销某个用户对除一个模式之外的所有模式的所有特权,他仍
我有两组点,一组来自分析,另一组用于分析数据的后处理结果。 黑色的分析数据是散乱的。 用于结果的点是红色的。 这是同一地 block 上的两组: 我遇到的问题是:我将插值到红点上,但如您所见,有些红点
我是一名优秀的程序员,十分优秀!