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:
> 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
Table "public.food_foods"
Column | Type | Modifiers
------------------------------+------------------------+-----------------------------------------------------------------
food_id | integer | not null default
nextval('public.food_foods_food_id_seq'::text)
category_id | integer | not null
brand_id | integer | not null
source_id | integer | not null
description | character varying(255) | not null
base | real |
type | character(1) | not null default 'M'
created | date | not null default ('now'::text)::date
modified | date | not null default ('now'::text)::date
water | real |
kilojoules | real |
calories | real |
protein | real |
total_fat | real |
total_carbohydrate | real |
sugars | real |
starch_and_dextrins | real |
fiber | real |
calcium | real |
phosphorus | real |
iron | real |
sodium | real |
potassium | real |
magnesium | real |
zinc | real |
retinol_equivalent | real |
retinol | real |
beta_carotene_equivalent | real |
thiamin | real |
riboflavin | real |
niacin_equivalent | real |
niacin | real |
vitamin_c | real |
alcohol | real |
saturated_fatty_acids | real |
monounsaturated_fatty_acids | real |
poly_unsaturated_fatty_acids | real |
omega3_fatty_acids | real |
cholesterol | real |
folate | real |
caffeine | real |
ftiidx | txtidx |
in_palm | boolean | default true
brand_name | character varying(255) |
staff_id | integer |
Indexes: food_foods_pkey primary key btree (food_id),
food_foods_brand_id_idx btree (brand_id),
food_foods_category_id_brand_id_idx btree (category_id, brand_id),
food_foods_ftiidx_key gist (ftiidx),
food_foods_modified_key btree (modified)
Check constraints: "$1" ((("type" = 'M'::bpchar) OR ("type" = 'V'::bpchar)) OR ("type" = 'S'::bpchar))
Foreign Key constraints: $5 FOREIGN KEY (staff_id) REFERENCES admin_staff(staff_id) ON UPDATE NO ACTION ON DELETE SET
NULL,
$4 FOREIGN KEY (source_id) REFERENCES food_sources(source_id) ON UPDATE NO ACTION ON DELETE NO
ACTION,
$3 FOREIGN KEY (brand_id) REFERENCES food_brands(brand_id) ON UPDATE NO ACTION ON DELETE NO
ACTION,
$2 FOREIGN KEY (category_id) REFERENCES food_categories(category_id) ON UPDATE NO ACTION ON
DELETENO ACTION
Triggers: food_foods_ftiidx_trig
Table "public.users_users"
Column | Type | Modifiers
-----------------+--------------------------+----------------------------------------------------------
userid | integer | not null default nextval('users_users_userid_seq'::text)
firstname | character varying(255) | not null
lastname | character varying(255) | not null
email | character varying(255) | not null
username | character varying(32) | not null
password | character varying(32) | not null
admin | boolean | not null default 'f'
promo | boolean | not null default 'f'
joindate | date | not null default 'today'
country | character varying(30) | not null
postcode | character varying(20) | not null
suspended | boolean | not null default 'f'
address | character varying(255) |
suburb | character varying(255) |
state | character varying(255) |
city | character varying(255) |
sex | character(1) | not null
dob | date | not null
phone | character varying(30) |
expiry | date |
freebie | boolean | not null default 'f'
listed | boolean | default 'f'
last_time | timestamp with time zone |
last_browser | character varying(255) |
notify | boolean | default 't'
referrer | integer |
cc_number | text |
cc_name | text |
cc_type | text |
cc_expire_mon | text |
cc_expire_year | text |
recurring | boolean | not null default 'f'
meetings | boolean | default 't'
publicdiary | boolean | not null default 'f'
suspended_on | date |
suspended_off | date |
online | boolean | not null default true
message | boolean | not null default true
msgreceive | boolean | not null default true
recurring_id | character varying(255) |
cobrand_id | integer | not null
first_brand | integer | not null
last_brand | integer | not null
professional_id | integer |
publicjournal | boolean | not null default false
Indexes: users_users_pkey primary key btree (userid),
users_users_cobrand_id_key unique btree (cobrand_id, username),
expiry_users_users_key btree (expiry),
users_users_email_lower_idx btree (lower(email)),
users_users_professional_id_idx btree (professional_id) WHERE (professional_id IS NOT NULL),
users_users_referrer_idx btree (referrer) WHERE (referrer IS NOT NULL),
users_users_susp_off_idx btree (suspended_off) WHERE (suspended_off IS NOT NULL)
Check constraints: "$2" (NOT (recurring AND suspended))
"users_users_sex" ((sex = 'M'::bpchar) OR (sex = 'F'::bpchar))
Foreign Key constraints: $6 FOREIGN KEY (professional_id) REFERENCES professionals(user_id) ON UPDATE NO ACTION ON
DELETENO ACTION,
$5 FOREIGN KEY (last_brand) REFERENCES cobrands(cobrand_id) ON UPDATE NO ACTION ON DELETE NO
ACTION,
$4 FOREIGN KEY (first_brand) REFERENCES cobrands(cobrand_id) ON UPDATE NO ACTION ON DELETE NO
ACTION,
$3 FOREIGN KEY (cobrand_id) REFERENCES cobrands(cobrand_id) ON UPDATE NO ACTION ON DELETE NO
ACTION,
$1 FOREIGN KEY (referrer) REFERENCES users_users(userid) ON UPDATE NO ACTION ON DELETE SET
NULL
Triggers: RI_ConstraintTrigger_973531,
RI_ConstraintTrigger_973532,
RI_ConstraintTrigger_973541,
RI_ConstraintTrigger_973542,
RI_ConstraintTrigger_973545,
RI_ConstraintTrigger_973546,
RI_ConstraintTrigger_973561,
RI_ConstraintTrigger_973562,
RI_ConstraintTrigger_973563,
RI_ConstraintTrigger_973564,
RI_ConstraintTrigger_973567,
RI_ConstraintTrigger_973568,
RI_ConstraintTrigger_973571,
RI_ConstraintTrigger_973572,
RI_ConstraintTrigger_973573,
RI_ConstraintTrigger_973574,
RI_ConstraintTrigger_973761,
RI_ConstraintTrigger_973762,
RI_ConstraintTrigger_973783,
RI_ConstraintTrigger_973784
