在事务中包含 select 语句有什么意义? [英] What's the point to enclose select statements in a transaction?

问题描述

在事务中包含 select 语句有什么意义?我认为 select 语句只是从数据库中获取"数据，它们没有机会回滚某些东西，因为您无法更改数据.那么，这是否是说我们永远不需要在事务中放置 select 语句?我说得对吗?

What's the point to enclose select statements in a transaction? I think select statements are just "GET" data from the database, they don't have chance to rollback something, because you just can't change the data. So, does that to say we never need put select statements in a transaction? Am I right?

谢谢.

推荐答案

你说得对:在标准 隔离级别，读提交，你不需要在事务中包装select语句.无论您是否将 Select 语句包装在事务中，都将防止它们被脏读.

You're right: at the standard isolation level, read committed, you do not need to wrap select statements in transactions. Select statements will be protected from dirty reads whether you wrap them in a transaction or not.

connection 1:                          connection 2:

                                       begin transaction
                                       update user set name = 'Bill' where id = 1
select name from users where id = 1
                                       rollback transaction

select 语句不会读取回滚的更新:它们没有被包装在事务中并不重要.

The select statement will not read the rolled-back update: it doesn't matter that they are not wrapped in a transaction.

如果您需要可重复读取，则换行选择默认交易无济于事:

If you need repeatable reads, then wrapping selects in a default transaction doesn't help:

connection 1:                          connection 2:

begin transaction
select name from users where id = 1
                                       update user set name = 'Bill' where id = 1
select name from users where id = 1
commit transaction

begin 和 commit 语句在这里没有帮助:第二个 select 可能 读取旧名称，或者它可能读取新名称.

The begin and commit statements won't help here: the second select may read the old name, or it may read the new name.

但是，如果您在更高的隔离级别上运行，例如 serializable 或 repeatable read，则组将受到不可重复读取的保护:

However, if you run at a higher isolation level, like serializable or repeatable read, the group will be protected from non-repeatable reads:

connection 1:                          connection 2:

set transaction isolation level
    repeatable read
begin transaction
select name from users where id = 1
                                       update user set name = 'Bill' where id = 1
select name from users where id = 1              |
commit transaction                               |
                                                 |--> executed here

在这种情况下，update 将阻塞，直到第一个事务完成.

In this scenario, the update will block until the first transaction is complete.

很少使用更高的隔离级别，因为它们会减少可以同时在数据库中工作的人数.在最高级别，serializable，报告查询会停止任何更新活动.

Higher isolation levels are rarely used because they lower the number of people that can work in the database at the same time. At the highest level, serializable, a reporting query halts any update activity.

这篇关于在事务中包含 select 语句有什么意义?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！