What's the point to enclose select statements in a transaction?

Question

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.

Answer 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.