Обсуждение: 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:

> 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

> 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