Re: ACCESS EXCLUSIVE LOCK

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