Обсуждение: ACCESS EXCLUSIVE LOCK
This may be a newbie question, but according to the 7.4 docs, an ACCESS EXCLUSIVE lock is only acquired by the ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, and VACUUM FULL commands. However, when viewing pg_locks during the execution of a stored procedure that does not perform any of the above commands, I see that the table it is working on is locked by ACCESS EXCLUSIVE. I have also tested that I can not perform a simple SELECT on the locked table while the SP is executing. Can anyone provide some insight? Thanks
seth.m.green@gmail.com wrote: > This may be a newbie question, but according to the 7.4 docs, an ACCESS > EXCLUSIVE lock is only acquired by the ALTER TABLE, DROP TABLE, > REINDEX, CLUSTER, and VACUUM FULL commands. > > However, when viewing pg_locks during the execution of a stored > procedure that does not perform any of the above commands, I see that > the table it is working on is locked by ACCESS EXCLUSIVE. Is the SP executing a LOCK TABLE perchance? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On 2006-03-02, seth.m.green@gmail.com <seth.m.green@gmail.com> wrote: > No. Here is the offending SP: > > CREATE OR REPLACE FUNCTION update_my_cache() RETURNS void AS ' > BEGIN > > TRUNCATE TABLE my_cache_table; TRUNCATE is another command that takes an access exclusive lock. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
On 2006-03-02, seth.m.green@gmail.com <seth.m.green@gmail.com> wrote: >> TRUNCATE is another command that takes an access exclusive lock. > > The whole SP takes about 10 seconds to run total. The TRUNCATE command > only takes less than a second. However, the access exclusive lock is > held throughout the entire SP, not just during the execution of the > TRUNCATE command. Shouldn't TRUNCATE be releasing the lock as soon as > it finishes? No, locks are always held until the end of the transaction. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
On 2006-03-02, seth.m.green@gmail.com <seth.m.green@gmail.com> wrote: > First of all, thank you very much. I changed TRUNCATE to DELETE FROM > and my problem as been fixed. > > Is there any way to override that behavior? I know you can explicitly > lock tables, can you explicitly unlock tables? No. > Just to be clear, once I run a TRUNCATE command inside an SP, that > table that it acts upon will have an access exclusive lock on it until > the SP is finished? Until the transaction is finished, and since you can't commit from inside a function, that means the lock will be held _at least_ until the end of the SP. This is necessary in order for other concurrent transactions not to get incorrect results. (The difference between TRUNCATE and DELETE in this case is that TRUNCATE gives the table a new, empty, heap and indexes, deleting the old ones on commit; that means that it can't allow concurrent access to the table since it is going to delete old tuples that might otherwise still be visible to other transactions. DELETE on the other hand simply marks the old tuples as dead; remember to vacuum as needed to clean up.) -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
You have a URL for where in the docs it says that? Sounds like it needs to be updated to include TRUNCATE. On Wed, Mar 01, 2006 at 03:55:26PM -0800, seth.m.green@gmail.com wrote: > This may be a newbie question, but according to the 7.4 docs, an ACCESS > EXCLUSIVE lock is only acquired by the ALTER TABLE, DROP TABLE, > REINDEX, CLUSTER, and VACUUM FULL commands. > > However, when viewing pg_locks during the execution of a stored > procedure that does not perform any of the above commands, I see that > the table it is working on is locked by ACCESS EXCLUSIVE. > > I have also tested that I can not perform a simple SELECT on the locked > table while the SP is executing. > > Can anyone provide some insight? > > Thanks > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
On Fri, Mar 03, 2006 at 08:47:41PM -0600, Jim C. Nasby wrote: > You have a URL for where in the docs it says that? Sounds like it needs > to be updated to include TRUNCATE. http://www.postgresql.org/docs/7.4/static/explicit-locking.html#LOCKING-TABLES It doesn't say that only the listed commands acquire ACCESS EXCLUSIVE, just that certain commands do. TRUNCATE isn't shown. -- Michael Fuhr
No. Here is the offending SP: CREATE OR REPLACE FUNCTION update_my_cache() RETURNS void AS ' BEGIN TRUNCATE TABLE my_cache_table; INSERT INTO my_cache_table SELECT * FROM get_my_stuff_to_fill_cache_table(); RETURN; END ' LANGUAGE plpgsql; I've checked the SP get_my_stuff_to_fill_cache_table() that is run from within update_my_cache() and it does not cause locks. So it seems something else about the update_my_cache() SP is causing this access exclusive lock which prevents anything from even dirty reading the table for the entire time it executes (roughly 10 seconds);
> TRUNCATE is another command that takes an access exclusive lock. The whole SP takes about 10 seconds to run total. The TRUNCATE command only takes less than a second. However, the access exclusive lock is held throughout the entire SP, not just during the execution of the TRUNCATE command. Shouldn't TRUNCATE be releasing the lock as soon as it finishes?
First of all, thank you very much. I changed TRUNCATE to DELETE FROM and my problem as been fixed. Is there any way to override that behavior? I know you can explicitly lock tables, can you explicitly unlock tables? Just to be clear, once I run a TRUNCATE command inside an SP, that table that it acts upon will have an access exclusive lock on it until the SP is finished?
On Thu, Mar 02, 2006 at 06:16:00AM -0800, seth.m.green@gmail.com wrote: > > TRUNCATE is another command that takes an access exclusive lock. > > The whole SP takes about 10 seconds to run total. The TRUNCATE command > only takes less than a second. However, the access exclusive lock is > held throughout the entire SP, not just during the execution of the > TRUNCATE command. Shouldn't TRUNCATE be releasing the lock as soon as > it finishes? Nope. Locks stick around until the transaction finishes. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461