lock AccessShareLock on object 0/1260/0 is already held

Pavel Stehule <pavel.stehule@gmail.com> writes:

What is state of this issue?
http://archives.postgresql.org/pgsql-hackers/2011-09/msg00423.php

AFAICS we never identified the cause. It was pretty clear that
something was failing to clean up shared-memory lock data structures,
but not what that something was. The last productive suggestion was
to add process-exit-time logging of whether unreleased locks remain,
but if Dave ever did that, he didn't report back what he found.

Maybe you could add such logging on your machines.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2013/1/4 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

What is state of this issue?
http://archives.postgresql.org/pgsql-hackers/2011-09/msg00423.php

AFAICS we never identified the cause. It was pretty clear that
something was failing to clean up shared-memory lock data structures,
but not what that something was. The last productive suggestion was
to add process-exit-time logging of whether unreleased locks remain,
but if Dave ever did that, he didn't report back what he found.

probably I am able to find statement, that was canceled as last
"success" statement from our application logs. And probably it will be
LOCK ... or CREATE TABLE AS SELECT

Recheck on session end can help us to drop this leaked locks, but I
don't understand how it can help with finding with finding the cause?

Maybe you could add such logging on your machines.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello

2013/1/4 Pavel Stehule <pavel.stehule@gmail.com>:

2013/1/4 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

What is state of this issue?
http://archives.postgresql.org/pgsql-hackers/2011-09/msg00423.php

AFAICS we never identified the cause. It was pretty clear that
something was failing to clean up shared-memory lock data structures,
but not what that something was. The last productive suggestion was
to add process-exit-time logging of whether unreleased locks remain,
but if Dave ever did that, he didn't report back what he found.

probably I am able to find statement, that was canceled as last
"success" statement from our application logs. And probably it will be
LOCK ... or CREATE TABLE AS SELECT

Recheck on session end can help us to drop this leaked locks, but I
don't understand how it can help with finding with finding the cause?

I am sending a patch that removes a described issue

we use this patch on our productions servers with some others diagnostics

+ 	 * GOODDATA
+ 	 *
+ 	 * we would to diagnose how much often is proces startup find
+ 	 * orphaned PGPROCLOCKS. Don't use ereport, because process is
+ 	 * not fully prepared in this moment.
+ 	 */
+ 	{
+ 		int		i;
+ 		bool			fix_orphaned_locks = false;
+
+ 		for (i = 0; i < NUM_LOCK_PARTITIONS; i++)
+ 		{
+ 			if (!SHMQueueEmpty(&(MyProc->myProcLocks[i])))
+ 			{
+ 				fprintf(stderr, "ProcKill: found orphaned PGPROCLOCK proc:%p,
pid:%d\n", MyProc, MyProcPid);
+ 				fix_orphaned_locks = true;
+ 				break;
+ 			}
+ 		}
+
+ 		if (fix_orphaned_locks)
+ 		{
+ 			LockReleaseAll(DEFAULT_LOCKMETHOD, true);
+ 			/* Release transaction-level advisory locks */
+ 			LockReleaseAll(USER_LOCKMETHOD, false);
+ 			fix_orphaned_locks = false;
+
+ 			for (i = 0; i < NUM_LOCK_PARTITIONS; i++)
+ 			{
+ 				if (!SHMQueueEmpty(&(MyProc->myProcLocks[i])))
+ 				{
+ 					fprintf(stderr, "ProcKill: found orphaned PGPROCLOCK proc:%p,
pid:%d again\n", MyProc, MyProcPid);
+ 					fix_orphaned_locks = true;
+ 					break;
+ 				}
+ 			}
+
+ 			if (fix_orphaned_locks)
+ 				fprintf(stderr, "ProcKill: orphaned PGPROCLOCK proc:%p, pid:%d
was removed\n", MyProc, MyProcPid);
+ 			else
+ 				fprintf(stderr, "ProcKill: orphaned PGPROCLOCK proc:%p, pid:%d
was not removed\n", MyProc, MyProcPid);
+ 		}
+ 	}
+

but it dosn't detect and removed any orphaned locks, so this code is
not necessary.

I can't to explain why moving initialization from proces to global
helps, but it just helps.

9.1 will be suppported next four years so maybe this patch can be
usefull for somebody or maybe can be merged

Regards

Pavel