strange behavior on locks
Hi,
I running PostgreSQL 8.0.x with database that does not have foreign keys
and sometimes the following messages appears:
ERROR: deadlock detected
DETAIL: Process 10029 waits for ShareLock on transaction 65272; blocked
by process 32436.
Process 32436 waits for ShareLock on transaction 65117; blocked
by process 10029.
I also see (right after executing a ps) that there are lots of messages
"select waiting".
1 - Does it mean that process 10029 owns transaction 65117 and
process 32436 owns transaction 65272 as a transaction acquires an
exclusive lock in its transaction id ?
2 - How is this situation possible ?
What could cause the status ("select waiting") ?
Best regards,
Alfranio
Alfranio Correia Junior <alfranio@lsd.di.uminho.pt> writes:
What could cause the status ("select waiting") ?
Perhaps you are using SELECT FOR UPDATE?
regards, tom lane
On Fri, Jan 20, 2006 at 11:47:55PM -0500, Tom Lane wrote:
Alfranio Correia Junior <alfranio@lsd.di.uminho.pt> writes:
What could cause the status ("select waiting") ?
Perhaps you are using SELECT FOR UPDATE?
or SELECT func_with_side_effects() ?
-Mike
Perhaps you are using SELECT FOR UPDATE?
I am not using "SELECT FOR UPDATES"...
Could it have another cause ?
I am executing some stored procedures written in pl/pgsql....
But wouldn't PostgreSQL show information per statement executed by the
functions ?
Show quoted text
or SELECT func_with_side_effects() ?
-Mike
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Alfranio Correia Junior <alfranio@lsd.di.uminho.pt> writes:
I am executing some stored procedures written in pl/pgsql....
But wouldn't PostgreSQL show information per statement executed by the
functions ?
No. The command shown in the process status is always just the
outermost operation.
regards, tom lane