Thread: Which SQL command creates ExclusiveLock?

Which SQL command creates ExclusiveLock?

From

"Denis Khabas"

Date:

26 April 2004, 18:15:01

Hi everyone!

I have a web application that uses Postgresql on backend. The application performs selects, updates, inserts, and deletes by using Hibernate. Tables

contain indexed fields. When I run the following query, SELECT * FROM pg_locks, it shows that some transactions place

ExclusiveLock:

relation | database | transaction | pid      |      mode       | granted
----------+----------+-------------+-------+-----------------+---------
    16757 |    16976 |                  | 22770 | AccessShareLock | t
             |             |    17965163 | 22770 | ExclusiveLock   | t

According to postgres documentation, all update operations place ROW EXCLUSIVE MODE locks, and

EXCLUSIVE MODE is not automatically acquired by any postgres SQL command. So, which command places

ExclusiveLock??? I suspect that indexes can account for this behaviour, but couldn't find anything in the docs.

I am also wondering why there is nothing shown in "relation" column. I think it is supposed to display a table or index id or any other

object that is being locked.

Thanks for help!

Dennis

Re: Which SQL command creates ExclusiveLock?

From

Tom Lane

Date:

28 April 2004, 04:16:36

"Denis Khabas" <dkhabas@bluecatnetworks.com> writes:
> According to postgres documentation, all update operations place ROW EXCLUS=
> IVE MODE locks, and=20
> EXCLUSIVE MODE is not automatically acquired by any postgres SQL command. S=
> o, which command places
> ExclusiveLock?

It says that no SQL command acquires ExclusiveLock *on a table*.  The
pg_locks row you show represents ExclusiveLock on a transaction number.
Every transaction gets ExclusiveLock on its transaction number for the
duration of its existence.
        regards, tom lane