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



Thu, 02 Oct 2003 16:29:14 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Weird locking situation
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

Thu, 02 Oct 2003 10:30:58 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Weird locking situation 
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



Thu, 02 Oct 2003 17:47:40 +0300
Hannu Krosing <hannu@tm.ee>
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
Fri, 03 Oct 2003 10:07:02 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Weird locking situation

> 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



Fri, 03 Oct 2003 10:13:09 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Weird locking situation
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

Fri, 03 Oct 2003 09:39:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Weird locking situation 
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

Fri, 3 Oct 2003 18:48:42 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: Weird locking situation 

> 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



Sat, 04 Oct 2003 17:38:39 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: Weird locking situation