Re: PG 8.3.3 - ERROR: lock AccessShareLock on object 16385/16467/0 is already held

[ reincluding the mailing list ]

Michael Milligan <milli(at)acmeps(dot)com> writes:
> Okay, it reproduces and surprise surprise nLocks does overflow...

> ERROR: lock AccessShareLock on object 16385/16467/0 is already held
> lock(0x87408a028) id(16385,16467,0,0,0,1) grantMask(a) waitMask(0)
> req(2,0,1,0,0,0,0,0)=3 grant(1,0,1,0,0,0,0,0)=2 wait(0)
> proclock(0x8743a7508) lock(0x87408a028) method(1) proc(0x8743aada8) hold(a)
> locallock(0xb29c78) nLocks(-2147483648) nOwners(2) mOwners(8)

Hah. Okay, that shows that we'd never have reproduced it with a small
test case.

> So I'm guessing this is not a bug? Just that for some reason 8.3.3 is
> grabbing more locks than 8.2.6 does and I have to adjust my batch
> processes to break things up into chunks of less than 10 million per
> transaction... :-/

Well, I'm wondering exactly why 8.3 is taking more locks than 8.2 does,
because AFAICS it shouldn't. Could you extract a complete example of
what your code does per tuple? I would like to run a small test case
and just see where the LockAcquire calls come from in each version.
(Or, if you prefer, you can get out gdb and do the legwork yourself...)
It's true that breaking up the transaction is likely to be the best
short-term answer for you, but I think there might be a bug here.

In any case, now that we know that nLocks overflow is actually possible
within real-world transaction lengths, it'd behoove us to do something
about that in 8.4 or beyond.

regards, tom lane