<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 <[EMAIL PROTECTED]> 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 DELETE NO 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 DELETE NO 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
