Hi guys,
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.
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.
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.
You might not be changing the data, but some other database connection could.
If you're sure that all that is happening is a SELECT, then it doesn't need to be in a transaction. Are you 100% sure that now and forever more it's going to be just a SELECT?
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.
You're right: at the standard isolation level, read committed, wrapping select statements in a transaction does not provide any benefits. A group of select statements will be protected from dirty reads whether you wrap them in a transaction or not.
transaction 1 transaction 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
Neither select statement will read the updated user name: it doesn't matter that they are not wrapped in a transaction. For the second class of isolation problems, repeatable read, wrapping selects in a transaction also doesn't provide any benefits:
transaction 1 transaction 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 will still 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:
transaction 1 transaction 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.
Another reason to use transactions with a select:
It's possible at some point you may want to invoke your select method from other methods that are participating in a transaction and you want the select to participate in the current transaction. If you have a consistent design where all database actions are performed in a transaction then callers of any method know that it will participate in their transaction.
For a small up front development cost, this could help avoid some fairly large changes later trying to shoehorn transactions in if/when requirements change or new requirements are added.