What's the point to enclose select statements in a transaction?
Solution 1
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
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
The begin
and commit
statements won't help here: the second select
may read the old name, or it may read the new name.
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
In this scenario, the update
will block until the first transaction is complete.
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.
Solution 2
You may be doing other updates/inserts during this transaction. If your code accessing the database is written in a reusable fashion then you may not whether selects are the only thing happening in the transaction.
Your select statements may want to be consistent for the duration of the transaction, as well as being consistent with data changes happening in other transactions. You will want to set some sort of isolation level in your system to prevent dirty reads (reading uncommitted changes in another transaction) or phantom reads (reading committed changes in another transaction).
Needless to say, you will be better served by using transactions.
Solution 3
A single SELECT statement is atomic to start with - enclosing it in a transaction is redundant. If there are multiple SELECT statements, you are guaranteed that no one changed anything affecting any of them until all of them complete.
Solution 4
No.
A transaction gives you a consistent view of the database.
If you want your selects to return the same results when you repeat them, a transaction can provide that.
Solution 5
You might not be changing the data, but some other database connection could.
![Just a learner](https://i.stack.imgur.com/WA698.png?s=256&g=1)
Just a learner
Updated on June 03, 2022Comments
-
Just a learner about 2 years
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?
Thanks.
-
Pacerier over 9 yearsRegarding your last paragraph, Why will the update block? Isn't it possible that the update will still go through without blocking, but the first transaction being repeatable-read, will still continue working with the old values?
-
Andomar over 9 years@Pacerier: Repeatable-read says that if you read twice, the second read will return the same result. Unlike Oracle or PostgeSQL, SQL Server doesn't keep old values around. You can change this behavior with
SET READ_COMMITTED_SNAPSHOT ON
. -
Pacerier over 9 yearsHmm, this is odd, seems to be the default on MySQL. So for sql server, after running
set read_committed_snapshot on
, is it right to say that the "update" will no longer block? -
Andomar over 9 years@Pacerier: MySQL is not capable of MVCC. MySQL's default is to return inconsistent data. With
read_committed_snapshot
, a read will not block an update, but one update still blocks another. To achieve that, SQL Server has to do way more disk I/O. -
Konrad almost 6 yearslack of details. I think Andomar's answer is the most accurate
-
janm almost 6 years@Konrad You're responding like this to an answer written less than five minutes after the question was asked, over eight years ago?
-
Konrad almost 6 yearsYes, I am. It's still an answer.