locking problems

On Sat, Mar 16, 2002 at 11:42:57AM -0800, Jonathan Ellis wrote:

I guess I don't understand locking in PG... I have a simple statement that
is deadlocking:

update minions set hp = hp_max

there are no triggers or rules on this table. Even when I try

lock table minions in row share mode

it still deadlocks. How can a statement like this deadlock? Doesn't it
acquire all necessary locks atomically?

Is that the only statement in the transaction?
How many rows are there in that table?
How do you know it's deadlocking?

HTH,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Are you sure it's deadlocking? I.e. it's rolling back because of
"Deadlock detected" errors?

That's right.

How can a statement like this deadlock? Doesn't it
acquire all necessary locks atomically?

Yes. The problem is in the case where another transaction is holding
something on the table. Postgres has a deadlock_timeout feature
which is there to prevent clients from waiting forever. What's yours
set at? Maybe you just need to set it higher.

I haven't changed this from the default (~20 seconds?). Is it a strict
first-in-first-out queue? Because there's a lot of other transactions
trying to update smaller portions of this table that seem to be cutting in
front of the line for the lock so to speak.

-Jonathan

On Sun, Mar 17, 2002 at 08:54:18PM -0800, Jonathan Ellis wrote:

something on the table. Postgres has a deadlock_timeout feature
which is there to prevent clients from waiting forever. What's yours
set at? Maybe you just need to set it higher.

I haven't changed this from the default (~20 seconds?). Is it a strict
first-in-first-out queue? Because there's a lot of other transactions
trying to update smaller portions of this table that seem to be cutting in
front of the line for the lock so to speak.

The docs say that if something is locked, the system waits
deadlock_timeout milliseconds before trying to discover whether the
condition can ever become unlocked. I ran into a problem with
deadlocks under heavy load once, and discovered that setting
deadlock_timeout higher did just what the docs suggested: "Ideally
the setting should exceed your typical transaction time, so as to
improve the odds that the lock will be released before the waiter
decides to check for deadlock." Maybe that's your problem, too.

A

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Andrew Sullivan <andrew@libertyrms.info> writes:

The docs say that if something is locked, the system waits
deadlock_timeout milliseconds before trying to discover whether the
condition can ever become unlocked. I ran into a problem with
deadlocks under heavy load once, and discovered that setting
deadlock_timeout higher did just what the docs suggested: "Ideally
the setting should exceed your typical transaction time, so as to
improve the odds that the lock will be released before the waiter
decides to check for deadlock." Maybe that's your problem, too.

AFAIK changing deadlock_timeout cannot introduce or remove deadlock
failures. It's purely an efficiency consideration, ie, how much
time is wasted on useless deadlock checks (useless because they find
no deadlock condition), vs how soon you find out about it when you
really do have a deadlock.

Jonathan's problem evidently is a genuine deadlock, and as such
twiddling deadlock_timeout isn't gonna help him. But he hasn't given
enough detail about what he's doing to let anyone understand why he's
hitting a deadlock.

regards, tom lane

On Tue, Mar 19, 2002 at 10:55:37AM -0500, Tom Lane wrote:

AFAIK changing deadlock_timeout cannot introduce or remove deadlock
failures. It's purely an efficiency consideration, ie, how much
time is wasted on useless deadlock checks (useless because they find
no deadlock condition), vs how soon you find out about it when you
really do have a deadlock.

Well, that's what I'd have thought, too, except for that last line in
the docs. I was, however, surprised when the number of deadlock
detections did in fact go down when I increased the timeout. Since I
wasn't able to figure out what was causing the deadlocks (I had a
couple of guesses, but I was never able to reproduce the problem
consistently), I was also relieved that it helped.

A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110

The docs say that if something is locked, the system waits
deadlock_timeout milliseconds before trying to discover whether the
condition can ever become unlocked. I ran into a problem with
deadlocks under heavy load once, and discovered that setting
deadlock_timeout higher did just what the docs suggested: "Ideally
the setting should exceed your typical transaction time, so as to
improve the odds that the lock will be released before the waiter
decides to check for deadlock." Maybe that's your problem, too.

the thing is, it shouldn't be deadlocking, so the "check for deadlock" code
should always look at it and say, "Hmm, nope, that'll take care of itself
eventually." So if it is deadlocking, it's a bug, and if it's whacking a
non-deadlocked transaction, that's a bug too the way I read it.

-Jonathan

Jonathan's problem evidently is a genuine deadlock, and as such
twiddling deadlock_timeout isn't gonna help him. But he hasn't given
enough detail about what he's doing to let anyone understand why he's
hitting a deadlock.

uhm. I don't know what to tell you besides what I already have, which is, a
simple "update mytable set field1=field2" is deadlocking, even when I lock
the table first. to my simple mind this sounds like a bug; if it's not,
what am I misunderstanding?

-Jonathan