Shinya Kato: 5 PostgreSQL locking behaviors that trip people up
This article discusses five PostgreSQL locking behaviors that can lead to unexpected issues. It highlights how certain locking mechanisms can cause queries to block each other, resulting in longer execution times or even outages. Understanding these behaviors is crucial for effective database management and performance optimization.
- ▪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, particularly when long-running SELECT statements are involved.
- ▪Foreign key constraints can lead to invisible deadlocks if two sessions attempt to lock different rows in a parent table in opposite orders.
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 Community.
Discussion
0 commentsMore from DEV Community
