0

I understand, the SQL transactions are particularly helpful in case of write queries i.e. when there are multiple concurrent transactions involving write queries, One will get the error on commit if the another transaction has already committed before it, resulting in the updated data that first transaction might not have. so that it doesn't blindly update the data and doesn't stay oblivious to the previous modification that another transaction has done.

I also understand the "atomicity" aspect of the databases, a set of operations will be atomic, when during execution, Either all has to be executed successfully or if any error occurs rollback it completely (strictly no hybrid state)

I am particularly confused about the use of transaction during Read queries, What are the use-cases where transactions while reading the data is useful. (ex: May be lock the row when during reading, nobody should be able to modify it. Not sure if it's really a possible use case).

Prateek Jain
  • 1,504
  • 4
  • 17
  • 27
  • 1
    Try reading about "Isolation Levels". Then you might understand more the sense of Transactions during reading. Perhaps https://stackoverflow.com/questions/47441027/pessimistic-locking-vs-serializable-transaction-isolation-level/47476818#47476818 could also help – aschoerk Sep 20 '22 at 12:12
  • Your description of the "error upon commit" seems to be of the serializable isolation level, which is not the isolation level usually used in PostgreSQL. – jjanes Sep 20 '22 at 15:05

1 Answers1

1

One reason to use a multi-statement transaction with reading statements could be isolation.

The behavior varies depending on the database system you are using, but for example on PostgreSQL all queries in a single REPEATABLE READ transaction see the same state of the database, regardless of concurrent data modifications. This can be extremely useful for reports that consist of multiple queries and have to see a consistent state of the database.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263