What causes lock?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

What causes lock?

Frank Miles
Hi everyone...

  I have a python-based application which provides a user interface
to a Postgresql database.  The latest version of the application suddenly
lost the inability to insert new data in some of the tables.  The SQL
statement(s) used still work if manually typed in psql, but it appears as
though there are some locks that are persisting and prevent inserts and
updates when the application is still running.  Examining pg_locks shows a
number of locks (almost all of mode 'AccessShareLock').  Other inserts
and updates (to other tables) still work.  The database logs (default
noise level setting) don't show any errors.
  It would be really helpful to be able to be able to find out what
is causing the persistent locks.  Is there any way to determine what table(s),
function(s), or other database items are involved in the lock?  Ideally
what SQL statement(s) led to any given lock being set?  (well, I can dream
can't I?)  I've already done a "pg_dump -o"; the relation values show in
pg_locks don't appear in the dump.  Is there some way to interpret the
relation values?

  I tried searching the documentation, including the list archives,
but didn't see the answer (not even 'you cant get there from here').  I'd
happily accept that I'm wrong -- feel free to point out the documentation
that lays this all out.

  Oh yes.  Postgresql 8.0.3 on Linux (Debian/standard testing package).

TIA!

  -frank

P.S. Yes, I'm going to be running various truncations of the user application
to investigate this as well, though it's pretty big by now and this won't
be pretty.  It would be nice to have a more general approach to this kind
of problem.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: What causes lock?

Jaime Casanova
On 8/4/05, Frank Miles <[hidden email]> wrote:

> Hi everyone...
>
>        I have a python-based application which provides a user interface
> to a Postgresql database.  The latest version of the application suddenly
> lost the inability to insert new data in some of the tables.  The SQL
> statement(s) used still work if manually typed in psql, but it appears as
> though there are some locks that are persisting and prevent inserts and
> updates when the application is still running.  Examining pg_locks shows a
> number of locks (almost all of mode 'AccessShareLock').  Other inserts
> and updates (to other tables) still work.  The database logs (default
> noise level setting) don't show any errors.
>

AccessShareLocks are safe, it's the type of lock a SELECT does.

The fact that you can execute the statements in psql show that the
problem is not about locking...

Are you sure there isn't a problem on your client side?


--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: What causes lock?

Michael Fuhr
In reply to this post by Frank Miles
On Thu, Aug 04, 2005 at 03:03:47PM -0700, Frank Miles wrote:
> I have a python-based application which provides a user interface
> to a Postgresql database.  The latest version of the application suddenly
> lost the inability to insert new data in some of the tables.  The SQL
> statement(s) used still work if manually typed in psql, but it appears as
> though there are some locks that are persisting and prevent inserts and
> updates when the application is still running.  Examining pg_locks shows a
> number of locks (almost all of mode 'AccessShareLock').  Other inserts
> and updates (to other tables) still work.  The database logs (default
> noise level setting) don't show any errors.

Do any of the locks have granted = f?  That's what you want to look
for if you're seeing blocking.  But if the statement works in psql
when it would block or fail in your application, then the problem
might lie elsewhere.

> It would be really helpful to be able to be able to find out what
> is causing the persistent locks.  Is there any way to determine what
> table(s),
> function(s), or other database items are involved in the lock?

You can get the relation name by casting the relation column to
regclass:

SELECT relation::regclass AS relname, * FROM pg_locks;

Note that this will resolve only relation names in the current
database.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: What causes lock??

Frank Miles
On Thu, 4 Aug 2005, Michael Fuhr wrote:

> On Thu, Aug 04, 2005 at 03:03:47PM -0700, Frank Miles wrote:
>> I have a python-based application which provides a user interface
>> to a Postgresql database.  The latest version of the application suddenly
>> lost the inability to insert new data in some of the tables.  The SQL
>> statement(s) used still work if manually typed in psql, but it appears as
>> though there are some locks that are persisting and prevent inserts and
>> updates when the application is still running.  Examining pg_locks shows a
>> number of locks (almost all of mode 'AccessShareLock').  Other inserts
>> and updates (to other tables) still work.  The database logs (default
>> noise level setting) don't show any errors.
>
> Do any of the locks have granted = f?  That's what you want to look
> for if you're seeing blocking.  But if the statement works in psql
> when it would block or fail in your application, then the problem
> might lie elsewhere.

Sorry for being unclear (to you too, Jaime).  The statements will _not_
execute in psql when the application is running; they will only execute
when the app has been shut down.  That is, in psql the SQL statement hangs
until the app exits.  This is for the forced case, see below.

>> It would be really helpful to be able to be able to find out what
>> is causing the persistent locks.  Is there any way to determine what
>> table(s),
>> function(s), or other database items are involved in the lock?
>
> You can get the relation name by casting the relation column to
> regclass:
>
> SELECT relation::regclass AS relname, * FROM pg_locks;

Aaaahhh!  That looks helpful.  Thanks!!

> Note that this will resolve only relation names in the current
> database.

All show granted='t' with just the app running.  If I really force things, and
try to update the same bit of data from psql, yes I can get a granted= 'f',
but that clearly isn't what's happening with the app failing all by
itself.  By the way, in this forced condition, the rows that show granted='f'
have blank relname, relation, and database fields :(

Well... I'm less and less inclined to think that the update/insert failure
is due to a lock problem.  Weird!  Perhaps the python/psycopg interface?
I've never had a case where the python/psycopg invocation worked differently
than with the psql interface.  It may yet be time to build the psycopg from
source, as the Debian version is pretty old...

> --
> Michael Fuhr
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to [hidden email] so that your
>       message can get through to the mailing list cleanly
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: What causes lock??

Tom Lane-2
Frank Miles <[hidden email]> writes:
> ... By the way, in this forced condition, the rows that show granted='f'
> have blank relname, relation, and database fields :(

Those would be locks on transaction IDs, which is what you see in
pg_locks when someone is blocked on a row-level lock.  (For reasons
of implementation efficiency, we don't record individual row locks
in a way that lets pg_locks see them :-()

This is definitely theorizing in advance of the evidence, but
I'm betting that your problem is due to locking of rows referenced
by foreign keys.  Did you recently add some foreign key constraints
to your database?

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: What causes lock??

Frank Miles
In reply to this post by Frank Miles
Following up on my question regarding locking...

>Well... I'm less and less inclined to think that the update/insert failure
>is due to a lock problem.  Weird!  Perhaps the python/psycopg interface?
>I've never had a case where the python/psycopg invocation worked differently
>than with the psql interface.  It may yet be time to build the psycopg from
>source, as the Debian version is pretty old...

The problem has been resolved... ok, I've been dense.  A "minor" change resulted
in going down a slightly different pathway in a python function (that I wrote)
that handles certain python-database exchanges.  This alternate pathway had
a bug (now expunged) that caused it to begin a transaction, but failed to
rollback or commit.  Doh!  This family of functions has been used for years
without finding the particular combination that excited this annoying behavior,
but it got me this time!  Added another test to the regression set...

My apologies to Federico (psycopg), which was blameless, and my continued
thanks to the Postgresql community (particularly Michael and Tom) for their
help!

  -frank

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly