postgresql - Performancescalability difference between different transaction isolation levels - Stack Overflow

admin管理员组

文章数量:1415697

I recently did some work around wrapping a set of select queries into a transaction. I was looking at both Repeatable Read and Serializable/Snapshot isolation.

Postgres has done some work on performance of deferrable read-only queries (Serializable Snapshot Isolation (SSI) and Predicate Locking). An earlier stack overflow question on this topic is Does PostgreSQL run some performance optimizations for read-only transactions .

The problem that I am trying to solve is figuring out the relative impact of the two. The previous stack overflow question had discussion around the optimization, but nothing on how to figure out which approach is better. Metrics are good, but it's hard to measure this under sufficient load to demonstrate the impact of locking.

• Is there something akin to query plans for locking?
• If not, what should I be looking at?

I recently did some work around wrapping a set of select queries into a transaction. I was looking at both Repeatable Read and Serializable/Snapshot isolation.

Postgres has done some work on performance of deferrable read-only queries (Serializable Snapshot Isolation (SSI) and Predicate Locking). An earlier stack overflow question on this topic is Does PostgreSQL run some performance optimizations for read-only transactions .

The problem that I am trying to solve is figuring out the relative impact of the two. The previous stack overflow question had discussion around the optimization, but nothing on how to figure out which approach is better. Metrics are good, but it's hard to measure this under sufficient load to demonstrate the impact of locking.

• Is there something akin to query plans for locking?
• If not, what should I be looking at?

• postgresql
• performance
• transactions
• database-performance

Share Improve this question Follow edited Feb 4 at 20:29 jarlh 44.8k88 gold badges5050 silver badges6767 bronze badges asked Feb 4 at 18:50 Erick TErick T 7,4591111 gold badges5454 silver badges9090 bronze badges 5

• You can track locks in pg_locks. Here's a recent thread about problematic locking in serializable with a demo showing those locks. – Zegarek Commented Feb 4 at 19:59
• Thanks for the link to that question. pg_locks returns the currently active locks, no? How would I use with the queries? Do I run it before/after the query executes? – Erick T Commented Feb 4 at 20:10
• During or after. Locks typically span a transaction, so you can see them there from the moment they got acquired until they get released with commit/rollback of their parent transaction. Advisory locks are an exception - you can acquire a session-level lock that outlasts transactions. To get those back they needs to be explicitly released from within the acquirer's session, or their session needs to die. – Zegarek Commented Feb 4 at 20:53
• Switch on log_lock_waits and adjust deadlock_timeout to collect some stats about how much your queries have to wait around in queues. – Zegarek Commented Feb 4 at 20:54
• 1 Sorry, it's too late to edit my comment above: the locks are visible from the moment they got requested, not acquired. Otherwise you wouldn't be able to see the queue – Zegarek Commented Feb 5 at 9:57

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

The REPEATABLE READ isolation level has even less direct impact on performance than the default READ COMMITTED isolation level. The reason for that is that with READ COMMITTED, every statement has to take a snapshot of the database, while with REPEATABLE READ, only the first statement in a transaction takes a snapshot, which is retained for the whole transaction.

The only consideration is that long read-only REPEATABLE READ transactions (unlike READ COMMITTED) block the progress of autovacuum, which can lead to table bloat, which in turn can affect performance. So make sure that you don't have long-running transactions.

SERIALIZABLE will incur a performance cost because of the additional predicate locks.

---

Don't fet that with both REPEATABLE READ and SERIALIZABLE you can get serialization errors, which require you to repeat transactions. Having to repeat transactions frequently can also affect performance.

本文标签： postgresqlPerformancescalability difference between different transaction isolation levelsStack Overflow