5 PostgreSQL locking behaviors that trip people up
PostgreSQL's locking behaviors can lead to unexpected conflicts and performance issues. The article discusses five specific locking behaviors that can cause queries to take longer than anticipated or even result in outages. Understanding these behaviors is crucial for database administrators to mitigate potential problems.
- ▪PostgreSQL uses Multi-Version Concurrency Control (MVCC) to manage concurrency, allowing reads and writes to occur simultaneously without blocking each other.
- ▪An ACCESS EXCLUSIVE lock can cause a chain of blocking queries, leading to significantly longer wait times for subsequent SELECT statements.
- ▪Foreign key constraints can lead to invisible deadlocks when multiple sessions attempt to lock rows in a conflicting manner.
Opening excerpt (first ~120 words) tap to expand
try { if(localStorage) { let currentUser = localStorage.getItem('current_user'); if (currentUser) { currentUser = JSON.parse(currentUser); if (currentUser.id === 2629632) { document.getElementById('article-show-container').classList.add('current-user-is-article-author'); } } } } catch (e) { console.error(e); } Shinya Kato Posted on May 26 5 PostgreSQL locking behaviors that trip people up #database #postgres Introduction PostgreSQL uses MVCC (Multi-Version Concurrency Control) for concurrency control: reads never block writes, and writes never block reads. Its locking system has 8 table-level lock modes and 4 row-level lock modes, and the conflict tables in the documentation tell you exactly which lock modes conflict with which.
…
Excerpt limited to ~120 words for fair-use compliance. The full article is at DEV.to (Top).
Discussion
0 commentsMore from DEV.to (Top)
