Weird locking situation
Hi guys,
I'm just trying to understand this situation:
Session 1
---------
BEGIN;
SELECT * FROM tab WHERE id=1 FOR UPDATE;
Session 2
---------
UPDATE tab SET blah=1 WHERE id=1;
<waits>
Session 1
---------
UPDATE tab SET blah=1 WHERE id=1;
ERROR: deadlock detected
Session 2
---------
...update has gone through.
What is going on here? Surely getting a FOR UPDATE row lock should
prevent another process getting an update lock?
Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
What is going on here? Surely getting a FOR UPDATE row lock should
prevent another process getting an update lock?
I could not duplicate your results. I did
regression=# create table tab(id int , blah int);
CREATE TABLE
regression=# insert into tab values(1,1);
INSERT 320558 1
regression=# insert into tab values(1,2);
INSERT 320559 1
regression=# insert into tab values(2,3);
INSERT 320560 1
regression=# BEGIN;
BEGIN
regression=# SELECT * FROM tab WHERE id=1 FOR UPDATE;
id | blah
----+------
1 | 1
1 | 2
(2 rows)
<< in another window >>
regression=# UPDATE tab SET blah=1 WHERE id=1;
[waits]
<< back to first window >>
regression=# UPDATE tab SET blah=1 WHERE id=1;
UPDATE 2
regression=# end;
COMMIT
<< second window now reports >>
UPDATE 2
regression=#
The behavior you describe would certainly be a bug, but you'll have to
show a reproducible example to convince me it wasn't pilot error. One
idea that springs to mind is that maybe additional rows with id=1 were
inserted (by some other transaction) between the SELECT FOR UPDATE and
the UPDATE?
regards, tom lane
Tom Lane kirjutas N, 02.10.2003 kell 17:30:
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
What is going on here? Surely getting a FOR UPDATE row lock should
prevent another process getting an update lock?
The behavior you describe would certainly be a bug, but you'll have to
show a reproducible example to convince me it wasn't pilot error. One
idea that springs to mind is that maybe additional rows with id=1 were
inserted (by some other transaction) between the SELECT FOR UPDATE and
the UPDATE?
Perhaps he was looking for "key locking", so thet "select ... where
key=1 for update" would also prevent inserts where key=1 ?
------------
Hannu
OK, I tried it again and it still seems buggy to me...
<session 1>
australia= begin;
BEGIN
australia=# select * from food_foods where food_id = 21 for update;
food_id | category_id | brand_id | source_id | description
| base | type | created | modified | water | kilojoules |
calories | protein | total_fat | total_carbohydrate | sugars |
starch_and_dextrins | fiber | calcium | phosphorus | iron | sodium |
potassium | magnesium | zinc | retinol_equivalent | retinol |
beta_carotene_equivalent | thiamin | riboflavin | niacin_equivalent |
niacin | vitamin_c | alcohol | saturated_fatty_acids |
monounsaturated_fatty_acids | poly_unsaturated_fatty_acids |
omega3_fatty_acids | cholesterol | folate | caffeine |
ftiidx | in_palm | brand_name | staff_id
---------+-------------+----------+-----------+----------------------------+------+------+------------+------------+-------+------------+----------+---------+-----------+--------------------+--------+---------------------+-------+---------+------------+------+--------+-----------+-----------+------+--------------------+---------+--------------------------+---------+------------+-------------------+--------+-----------+---------+-----------------------+-----------------------------+------------------------------+--------------------+-------------+--------+----------+-----------------------------------------------+---------+------------------------+----------
21 | 91 | 1 | 2 | Spirits: Brandy (40%
Alc.) | 100 | V | 2000-07-01 | 2002-06-18 | 66.4 | 858 |
207 | 0 | 0 | 0.3 | 0.3 |
| | | | | 2 | |
| | | |
| | | | | |
29.4 | 0 | |
| | | |
| '40' 'alc' 'brand' 'averag' 'brandi' 'spirit' | t | -
Average All Brands - |
(1 row)
<session 2>
australia=# update food_foods set calories=208 where food_id=21;
<waits>
<session 1>
australia=# update food_foods set calories=207 where food_id=21;
ERROR: deadlock detected
<session 2>
UPDATE 1
But strangely enough, it works just fine on another table:
<session 1>
australia=# begin;
BEGIN
australia=# select * from users_users where userid=1 for update;
userid | firstname | lastname | email |
username | password | admin | promo | joindate
| country | postcode | suspended | address | suburb |
state | city | sex | dob | phone | expiry | freebie
| listed | last_time |
last_browser |
notify | referrer | cc_number | cc_name | cc_type | cc_expire_mon |
cc_expire_year | recurring | meetings | publicdiary | suspended_on |
suspended_off | online | message | msgreceive | recurring_id |
cobrand_id | first_brand | last_brand | professional_id | publicjournal
--------+-------------+-------------+-----------------------------+----------+----------------------------------+-------+-------+------------+---------+----------+-----------+-----------------+-------------+-------+-------+-----+------------+----------------+------------+---------+--------+-------------------------------+------------------------------------------------------------------------------------------------+--------+----------+-----------+---------+---------+---------------+----------------+-----------+----------+-------------+--------------+---------------+--------+---------+------------+--------------+------------+-------------+------------+-----------------+---------------
1 | Christopher | Kings-Lynne | xxxxxxxxxxxxx | chriskl |
xxxxxxxxxxxxxxxxxxxxx| t | f | 2000-12-15 | AU | 6007 |
f | xxxxxxxxx | xxxxx| WA | Perth | M | 1978-05-01 |
xxxxxxx | 3002-02-02 | f | t | 2003-10-01 15:39:44.139815+08
| Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.5a)
Gecko/20030728 Mozilla Firebird/0.6.1 | t | | |
| | | | f | f
| t | | | t | t | f
| | 1 | 1 | 1 |
| t
(1 row)
<session 2>
australia=# update users_users set expiry='3000-01-01' where userid=1;
UPDATE 1
<session 1>
australia=# update users_users set expiry='3000-01-01' where userid=1;
UPDATE 1
australia=# commit;
COMMIT
Table definitions are attached. The RI_constraint triggers are there
because this is on our test database, and there's a bit of screwiness
with those constraints that adddepend couldn't fix.
I wonder if it's something to do with the tsearch trigger on food_foods?
Chris
Tom Lane wrote:
Show quoted text
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
What is going on here? Surely getting a FOR UPDATE row lock should
prevent another process getting an update lock?I could not duplicate your results. I did
regression=# create table tab(id int , blah int);
CREATE TABLE
regression=# insert into tab values(1,1);
INSERT 320558 1
regression=# insert into tab values(1,2);
INSERT 320559 1
regression=# insert into tab values(2,3);
INSERT 320560 1
regression=# BEGIN;
BEGIN
regression=# SELECT * FROM tab WHERE id=1 FOR UPDATE;
id | blah
----+------
1 | 1
1 | 2
(2 rows)<< in another window >>
regression=# UPDATE tab SET blah=1 WHERE id=1;
[waits]<< back to first window >>
regression=# UPDATE tab SET blah=1 WHERE id=1;
UPDATE 2
regression=# end;
COMMIT<< second window now reports >>
UPDATE 2
regression=#The behavior you describe would certainly be a bug, but you'll have to
show a reproducible example to convince me it wasn't pilot error. One
idea that springs to mind is that maybe additional rows with id=1 were
inserted (by some other transaction) between the SELECT FOR UPDATE and
the UPDATE?regards, tom lane
Attachments:
schema.txttext/plain; name=schema.txtDownload
I wonder if it's something to do with the tsearch trigger on food_foods?
Actually, it definitely seems to be the tsearch trigger. The deadlock
occurs on every table that uses tsearch trigger, and no table that doesn't.
It's probably not the tsearch trigger itself even, it's probably the
fact that the tsearch trigger modifies the row during the update in the
second session?
Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
OK, I tried it again and it still seems buggy to me...
I wonder if it's something to do with the tsearch trigger on food_foods?
I tried a table with a simple BEFORE trigger and it didn't fail.
But when I added a GIST index, it did:
[ install contrib/btree_gist ]
regression=# create index gindex on foo using gist (f2);
CREATE INDEX
...
regression=# UPDATE foo SET f2=now() WHERE f1=1;
ERROR: deadlock detected
DETAIL: Process 18122 waits for AccessExclusiveLock on relation 154635 of database 17139; blocked by process 18133.
Process 18133 waits for ShareLock on transaction 6330; blocked by process 18122.
The trouble here is that GIST indexes are not concurrency-safe.
This is on the TODO list but I fear it's not a small task ...
regards, tom lane
On Fri, 3 Oct 2003, Tom Lane wrote:
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
OK, I tried it again and it still seems buggy to me...
I wonder if it's something to do with the tsearch trigger on food_foods?
I tried a table with a simple BEFORE trigger and it didn't fail.
But when I added a GIST index, it did:[ install contrib/btree_gist ]
regression=# create index gindex on foo using gist (f2);
CREATE INDEX
...
regression=# UPDATE foo SET f2=now() WHERE f1=1;
ERROR: deadlock detected
DETAIL: Process 18122 waits for AccessExclusiveLock on relation 154635 of database 17139; blocked by process 18133.
Process 18133 waits for ShareLock on transaction 6330; blocked by process 18122.The trouble here is that GIST indexes are not concurrency-safe.
This is on the TODO list but I fear it's not a small task ...
You're right. We hoped to work on concurrency this year and already
did some research. But life is so complicated :(
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
regression=# UPDATE foo SET f2=now() WHERE f1=1;
ERROR: deadlock detected
DETAIL: Process 18122 waits for AccessExclusiveLock on relation 154635 of database 17139; blocked by process 18133.
Process 18133 waits for ShareLock on transaction 6330; blocked by process 18122.The trouble here is that GIST indexes are not concurrency-safe.
This is on the TODO list but I fear it's not a small task ...
Wow, that's bad. I always thought the TODO item was talking about poor
concurrent performance - not actual concurrency errors!
Chris