Current Transaction Isolation in PostgreSQL and Future Directions
Concurrent database transactions can interact in surprising ways. Users currently have various methods to deal with these issues within PostgreSQL, through:
- the choice between two transaction isolation levels,
- table locks programmed at the application level,
- the use of additional programmed updates,
- automatic retry of transactions rolled back due to conflicts,
- externally programmed locking techniques, or
- (most often) some combination of the above.
The underlying issues as well as current techniques will be covered, and a discussion of current efforts to add a third transaction isolation level which, when used consistently, would eliminate the need for any of the above except for the transaction retries.
The new mode would be of particular interest to those in shops with many programmers working with a large number of queries, particularly if ad hoc queries or generated queries are possible. (Common sources of generated queries are ORMs or other frameworks which generate queries dynamically based on user input.) In such environments it can currently be extremely difficult to ensure that no anomalies from concurrent transactions cause violations of complex data integrity rules enforced through triggers or other application programming, or that no read-only queries see views of data which are inconsistent with some sequence of transaction commits.