From: | REYNAUD Jean-Samuel <reynaud(at)elma(dot)fr> |
---|---|
To: | pgsql Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | FOR UPDATE lock problem ? |
Date: | 2006-04-25 14:32:57 |
Message-ID: | 1145975577.17719.21.camel@jsr.elma.loc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi all,
I had a lock problem on my database.
When I use a "select for update" request whitch uses an index, the
locking system is inconsistant.
Take this example:
test=# \d users
Table "public.users"
Column | Type | Modifiers
---------+---------+---------------------------------------------------------
id_user | integer | not null default
nextval('users_id_user_seq'::regclass)
name | text |
Indexes:
"users_pkey" PRIMARY KEY, btree (id_user)
test=# \d sessions
Table "public.sessions"
Column | Type | Modifiers
------------+-----------------------------+---------------------------------------------------------------
id_session | integer | not null default
nextval('sessions_id_session_seq'::regclass)
id_user | integer |
from_date | timestamp without time zone | default now()
to_date | timestamp without time zone |
Indexes:
"sessions_pkey" PRIMARY KEY, btree (id_session)
"idx_session_null" btree (id_session) WHERE to_date IS NULL
Foreign-key constraints:
"sessions_id_user_fkey" FOREIGN KEY (id_user) REFERENCES
users(id_user)
test =# INSERT INTO users (name) values ('bob');
test =# INSERT INTO users (name) values ('brad');
test =# INSERT INTO Sessions (id_user,to_date) select id_user,now() from
users;
x 200 times (for example)
test =# INSERT INTO Sessions (id_user) select id_user from users;
test =# ANALYSE Sessions;
test=# explain select s.id_session from users u, sessions s where
to_date IS NULL and u.id_user = s.id_user;
QUERY PLAN
-----------------------------------------------------------------------------------------
Nested Loop (cost=0.00..6.85 rows=1 width=4)
-> Index Scan using idx_session_null on sessions s (cost=0.00..1.01
rows=1 width=8)
-> Index Scan using users_pkey on users u (cost=0.00..5.82 rows=1
width=4)
Index Cond: (u.id_user = "outer".id_user)
(4 rows)
Then the problem with two backends:
bk1:
test=# begin;
test=# select s.id_session from users u, sessions s
where to_date IS NULL and u.id_user = s.id_user for update;
id_session
------------
403
404
(2 rows)
bk2:
test=# begin;
test=# select s.id_session from users u, sessions s
where to_date IS NULL and u.id_user = s.id_user for update;
=> ... Waiting
bk1:
test=# UPDATE sessions set to_date = now() where to_date is null;
UPDATE 2
test=# commit;
Then finaly on bk2:
id_session
------------
403
404
(2 rows)
=> But the rows were updated by the other backend so to_date field is
not null for these tuples...However these tuples are in the result
produced by the backend #2...
If I remove the idx_session_null index the problem disappears.
--
REYNAUD Jean-Samuel <reynaud(at)elma(dot)fr>
Elma
From | Date | Subject | |
---|---|---|---|
Next Message | Zeugswetter Andreas DCP SD | 2006-04-25 14:55:00 | Re: [GENERAL] Concurrency problem building indexes |
Previous Message | A.M. | 2006-04-25 14:28:27 | Re: [PATCHES] Implementing RESET CONNECTION ... |