Skip site navigation (1) Skip section navigation (2)

Re: Problem with table lock within a function

From: Daniel Cristian Cruz <danielcristian(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Problem with table lock within a function
Date: 2009-04-08 20:02:57
Message-ID: 48d0cacb0904081302v7382609at2092dc5aefad9dec@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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(at)gmail(dot)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
クルズ  クリスチアン ダニエル

In response to

pgsql-admin by date

Next:From: fatih ozturkDate: 2009-04-09 08:15:10
Subject: log_statements = mod or all
Previous:From: Tom LaneDate: 2009-04-08 20:02:31
Subject: Re: Problem with table lock within a function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group