Thread: locking problems
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? -Jonathan
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/ > Ignorance continues to thrive when intelligent people choose to do > nothing. Speaking out against censorship and ignorance is the imperative > of all intelligent people.
> 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