Thread: What causes lock?
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.
On 8/4/05, Frank Miles <fpm@u.washington.edu> 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 ;)
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
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 majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Frank Miles <fpm@u.washington.edu> 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