Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem with table lock within a function

Problem with table lock within a function

From
Daniel Cristian Cruz
Date:
Hi all,

I had a big function (the same one mentioned before) which is locking a table, where it doesn't use the table for updates, just selects. (PostgreSQL 8.2)

My pg_locks had:

sgn=# SELECT * FROM pg_locks WHERE relation = 1706665;
 locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction |  pid  |        mode         | granted
----------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+---------------------+---------
 relation |  1705022 |  1706665 |      |       |               |         |       |          |   160710046 | 20125 | AccessShareLock     | t
 relation |  1705022 |  1706665 |      |       |               |         |       |          |   160721896 | 24198 | AccessShareLock     | f
 relation |  1705022 |  1706665 |      |       |               |         |       |          |   160721876 | 25744 | AccessShareLock     | f
 relation |  1705022 |  1706665 |      |       |               |         |       |          |   160721874 | 25743 | AccessExclusiveLock | f
(4 registros)

What could be wrong? How could I get an access share lock only using select? Any way to avoid it? My fuction runs for 3 minutes and every developer is trying to kill me, because they are waiting for their results.

Regards,
--
Daniel Cristian Cruz
クルズ  クリスチアン ダニエル

Re: Problem with table lock within a function

From
Tom Lane
Date:
Daniel Cristian Cruz <danielcristian@gmail.com> writes:
> sgn=# SELECT * FROM pg_locks WHERE relation = 1706665;
>  locktype | database | relation | page | tuple | transactionid | classid |
> objid | objsubid | transaction |  pid  |        mode         | granted
>
----------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+---------------------+---------
>  relation |  1705022 |  1706665 |      |       |               |
> |       |          |   160721874 | 25743 | AccessExclusiveLock | f

> What could be wrong? How could I get an access share lock only using select?

Whatever is trying to take that AccessExclusiveLock is most certainly
not "only using select".  Have you looked into pg_stat_activity to
try to see what it *is* doing?

            regards, tom lane

Re: Problem with table lock within a function

From
Daniel Cristian Cruz
Date:
Sorry... Long time not using pg_locks...

My function (on pid 20125) was running, pid 25743 requested an exclusive lock and is waiting, and all others were waiting for this one.

2009/4/8 Daniel Cristian Cruz <danielcristian@gmail.com>
Hi all,

I had a big function (the same one mentioned before) which is locking a table, where it doesn't use the table for updates, just selects. (PostgreSQL 8.2)

My pg_locks had:

sgn=# SELECT * FROM pg_locks WHERE relation = 1706665;
 locktype | database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction |  pid  |        mode         | granted
----------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+---------------------+---------
 relation |  1705022 |  1706665 |      |       |               |         |       |          |   160710046 | 20125 | AccessShareLock     | t
 relation |  1705022 |  1706665 |      |       |               |         |       |          |   160721896 | 24198 | AccessShareLock     | f
 relation |  1705022 |  1706665 |      |       |               |         |       |          |   160721876 | 25744 | AccessShareLock     | f
 relation |  1705022 |  1706665 |      |       |               |         |       |          |   160721874 | 25743 | AccessExclusiveLock | f
(4 registros)

What could be wrong? How could I get an access share lock only using select? Any way to avoid it? My fuction runs for 3 minutes and every developer is trying to kill me, because they are waiting for their results.

Regards,
--
Daniel Cristian Cruz
クルズ  クリスチアン ダニエル



--
Daniel Cristian Cruz
クルズ  クリスチアン ダニエル