Re: Weird locking situation

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