选择、插入、删除时的事务隔离
如果并发用户在默认的 READ COMMITTED 隔离级别下执行以下事务，可能会出现什么问题？
BEGIN TRANSACTION
SELECT * FROM t WHERE pid = 10 and r between 40 and 60
-- ... this returns tid = 1, 3, 5
-- ... process returned data ...
DELETE FROM t WHERE tid in (1, 3, 5)
INSERT INTO t (tid, pid, r) VALUES (77, 10, 35)
INSERT INTO t (tid, pid, r) VALUES (78, 10, 37)
INSERT INTO t (tid, pid, r) VALUES (79, 10, 39)
COMMIT
What could possibly go wrong with the following transaction if executed by concurrent users in the default isolation level of READ COMMITTED?
BEGIN TRANSACTION
SELECT * FROM t WHERE pid = 10 and r between 40 and 60
-- ... this returns tid = 1, 3, 5
-- ... process returned data ...
DELETE FROM t WHERE tid in (1, 3, 5)
INSERT INTO t (tid, pid, r) VALUES (77, 10, 35)
INSERT INTO t (tid, pid, r) VALUES (78, 10, 37)
INSERT INTO t (tid, pid, r) VALUES (79, 10, 39)
COMMIT
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
死锁可能会导致严重的性能问题。SELECT
将获取页面上的共享锁，然后 DELETE 将尝试将这些锁升级为独占锁。
如果另一个用户正在执行相同的查询，则它可能会同时另一个用户获得同一页面上的共享锁。然后当一个尝试升级到独占锁时，它将等待所有其他共享锁被释放。另一个也将等待所有共享锁被释放。两者都将拥有共享锁，并等待对方释放该共享锁，以便自己可以获得排它锁。其他查询将堆积起来尝试执行相同的操作，很快就会开始检测到死锁，并且查询将开始被取消和回滚。根据查询的频率，数据库引擎的死锁检测可能不会像新查询进入一样快地终止查询，这意味着没有一个查询会成功。
您需要在选择中添加诸如提示之类的内容，以请求从一开始就获得独占锁。或者，您可以将选择移到事务之外，并在其他语句的 where 标准中使用并发冲突检测。
You could having serious performance problems from dead locks
The SELECT will obtain a shared lock on a page and then the DELETE would attempt to upgrade those locks to exclusive locks.
If another user was executing the same query, it might obtain shared locks on the same page at the same time another user does. Then when one tries to upgrade to an exclusive lock, it will wait for all other shared locks to be released. The other will also be waiting for all shared locks to be released. Both will have a shared lock and waiting for the other to release that shared lock so itself can obtain an exclusive lock. Other queries will pile up trying to do the same, and soon the deadlocks will begin to be detected and the queries will begin to get cancelled and rolled back. Depending on the frequency of the queries the dead lock detection of the DB engine may not be killing off queries as fast as new ones are coming in, meaning none of the queries will succeed.
You would need to add something like a hint in the select to request that an exclusive lock be obtained from the get-go. Or you could move the select outside of the transaction and use concurrency conflict detection in your other statement's where criteria.
整件事对我来说都很奇怪。选择的目的是什么？它什么也做不了。写一个删除来选择你想要的记录。让我震惊的并发用户问题是，他们将尝试插入相同的记录，因为您对值进行了硬编码，因此可能会遇到您对 tid 或 tid、pid 组合可能具有的独特约束。
老实说，你想在这里完成什么？这看起来像是一个临时查询，旨在供您尝试多次运行的一次性使用。像这样进行硬编码几乎总是一个坏主意。
Thew whole thing is strange to me. What is the purpose of the select? It accomplishes nothing. Write a delete to select the records you want. What would strike me a problem of concurrent users is that they will be trying to insert the same records since you hard-coded the values and thus would probably run into the unique constraints that you probably have on tid or the tid, pid combo.
Honestly what are you trying to accomplish here? This looks like an ad hoc query that was meant for one-time usage that you are trying to run multiple times. It is almost always a bad idea to hard-code like this.
你真的应该提到你是否使用 oracle 或者 postgres。
另外，您应该明确指定锁定而不是依赖默认行为。
它们可能会随着其他数据库或数据库版本而改变。
You really should mention if you're using oracle or postgres.
Also, you should explicitly specify your locking and not rely on default behavior.
They may change with other databases or database versions.
您没有对 SELECT 使用锁，因此每个人都会得到相同的结果，每个人都会看到记录 tid 1、3 和 5。每个人都会处理这些记录，每个人都会尝试删除这些记录。这是行不通的，删除操作会放置一个锁。只有一个事务可以锁定这些记录，所有其他事务必须等待第一个事务的提交。该事务将插入新记录并提交，所有其他事务将不删除任何内容（找不到记录，没问题）并插入新记录。这些记录具有相同的数据，有问题吗？
也许您需要 SELECT ... FROM ... FOR UPDATE; 来锁定要处理的记录。
http://www.postgresql.org/docs/8.4/interactive/ sql-select.html
You don't use a lock for the SELECT, so everybody will get the same results, everybody will see records tid 1, 3 and 5. Everybody will process these records and everybody will try to delete these records. And that's not going to work, the delete operation will place a lock. Only one transaction can lock these records, all other transactions have to wait for commit of the first transaction. This transaction will insert the new records and commit, all others will delete nothing (can't find records, no problem) and insert the new records as well. These records have the same data, is that a problem?
Maybe you want a
SELECT ... FROM ... FOR UPDATE;to lock the records you want to process.http://www.postgresql.org/docs/8.4/interactive/sql-select.html