Re: 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(dot)m(dot)green(at)gmail(dot)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

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);

On 2006-03-02, seth(dot)m(dot)green(at)gmail(dot)com <seth(dot)m(dot)green(at)gmail(dot)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

> 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?

On 2006-03-02, seth(dot)m(dot)green(at)gmail(dot)com <seth(dot)m(dot)green(at)gmail(dot)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

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 2006-03-02, seth(dot)m(dot)green(at)gmail(dot)com <seth(dot)m(dot)green(at)gmail(dot)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(dot)m(dot)green(at)gmail(dot)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(at)pervasive(dot)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

On Fri, Mar 03, 2006 at 08:00:19PM -0700, Michael Fuhr wrote:
> 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.

Patch against HEAD to add TRUNCATE to the list of commands that aquire
ACCESS EXCLUSIVE.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Patch applied. Thanks. Your documentation changes can be viewed in
five minutes using links on the developer's page,
http://www.postgresql.org/developer/testing.

---------------------------------------------------------------------------

Jim C. Nasby wrote:
> On Fri, Mar 03, 2006 at 08:00:19PM -0700, Michael Fuhr wrote:
> > 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.
>
> Patch against HEAD to add TRUNCATE to the list of commands that aquire
> ACCESS EXCLUSIVE.
> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com

+ If your life is a hard drive, Christ can be your backup. +

On Thu, Mar 02, 2006 at 06:16:00AM -0800, seth(dot)m(dot)green(at)gmail(dot)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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461