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(at)familyhealth(dot)com(dot)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