tag:blogger.com,1999:blog-20038672.post8339568411780158922..comments2024-03-28T00:58:29.187-04:00Comments on Robert Haas: Locking in PostgreSQLRobert Haashttp://www.blogger.com/profile/08393677427643988650noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-20038672.post-2639891502958477082014-08-26T22:16:47.427-04:002014-08-26T22:16:47.427-04:00One interesting case is when you issue an ALTER TA...One interesting case is when you issue an ALTER TABLE statement when an open transaction is holding the target table in say ACCESS SHARE mode. The ALTER TABLE statement has to wait until the ACCESS SHARE lock is released, so the ACCESS EXCLUSIVE lock is not granted yet, but subsequent accesses to the target table are blocked as if the ACCESS EXCLUSIVE lock were granted. How do we explain this behaviour?iCodeiExisthttps://www.blogger.com/profile/03548697428629139268noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-86206311357829773912014-03-04T16:15:03.969-05:002014-03-04T16:15:03.969-05:00Yes, SELECT, INSERT, UPDATE, and DELETE all take m...Yes, SELECT, INSERT, UPDATE, and DELETE all take mutually non-conflicting locks. But other operations like VACUUM and TRUNCATE take stronger locks.Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-66414691686298556362014-02-11T15:18:27.066-05:002014-02-11T15:18:27.066-05:00I wonder whether 'delete from' would work ...I wonder whether 'delete from' would work where 'truncate' hangs when a select statement produces a exclusive lock on the table? Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-6065225048552682011-06-26T23:42:42.096-04:002011-06-26T23:42:42.096-04:00Improving performance is great, but what I'd l...Improving performance is great, but what I'd like to see more of is visibility into what's happening. A way to expose more details on the spinlocks such as WALWriteLock can enable better debugging and tuning. As someone who has switched from using Oracle to PostgreSQL, I still feel partially blind as to what is happening in my databases :)Nathan Thomhttp://blog.kimiensoftware.comnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-67290790207999801062011-05-23T12:24:44.029-04:002011-05-23T12:24:44.029-04:00@Anonymous: I think you need to kill the idle tran...@Anonymous: I think you need to kill the idle transaction using pg_terminate_backend(). You might want to post to one of the mailing lists (pgsql-general or pgsql-admin, perhaps) for more help.Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-84437469070439879672011-05-16T19:59:46.495-04:002011-05-16T19:59:46.495-04:00I have the following scenario. One of the transact...I have the following scenario. One of the transactions seem to have got stuck ( it shows as idle) for about 5 days now. This has blocked the Vacuum from running and the vacuum job is in the ShareUpdateExclusiveLock mode. <br /><br />How do i go about recovering from the problem?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-79339720974670935342011-01-20T18:28:24.174-05:002011-01-20T18:28:24.174-05:00"This is because the SELECT statement has obt..."This is because the SELECT statement has obtained a lock on table s. It's a very lightweight sort of of lock..."<br /><br />I think that's a confusing choice of words. Although the lock strength is fairly weak (few conflicts); the lock level is called "heavyweight".<br /><br />To clarify for other readers, I am trying to distinguish between the *level* of the lock (e.g. heavyweight lock, like those visible in pg_locks; versus LWLocks, etc.) and the strength of the lock (shared, exclusive, etc.).Jeff Davishttp://thoughts.j-davis.comnoreply@blogger.com