Thread: FOR UPDATE lock problem ?
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
timezone | 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=1width=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@elma.fr>
Elma
REYNAUD Jean-Samuel <reynaud@elma.fr> writes:
> 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)
> If I remove the idx_session_null index the problem disappears.
Interesting example. The planner is assuming that it need not
explicitly check the to_date IS NULL condition as a plan filter
condition since it is using a partial index, but apparently in the case
of SELECT FOR UPDATE queries we need to check anyway so that
EvalPlanQual will work properly. Or maybe partial-index predicates
ought to be added to the EvalPlanQual mechanism.
regards, tom lane
REYNAUD Jean-Samuel <reynaud@elma.fr> writes:
> When I use a "select for update" request whitch uses an index, the
> locking system is inconsistant.
I've applied the attached patch to HEAD and 8.1 to fix this.
regards, tom lane
Index: createplan.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/optimizer/plan/createplan.c,v
retrieving revision 1.202.2.2
diff -c -r1.202.2.2 createplan.c
*** createplan.c 29 Jan 2006 18:55:55 -0000 1.202.2.2
--- createplan.c 25 Apr 2006 16:46:12 -0000
***************
*** 816,823 **** * are not equal to, but are logically implied by, the index quals; so we * also try a
predicate_implied_by()check to see if we can discard quals * that way. (predicate_implied_by assumes its first
inputcontains only
! * immutable functions, so we have to check that.) We can also discard
! * quals that are implied by a partial index's predicate. * * While at it, we strip off the
RestrictInfosto produce a list of plain * expressions.
--- 816,827 ---- * are not equal to, but are logically implied by, the index quals; so we * also try a
predicate_implied_by()check to see if we can discard quals * that way. (predicate_implied_by assumes its first
inputcontains only
! * immutable functions, so we have to check that.)
! *
! * We can also discard quals that are implied by a partial index's
! * predicate, but only in a plain SELECT; when scanning a target relation
! * of UPDATE/DELETE/SELECT FOR UPDATE, we must leave such quals in the
! * plan so that they'll be properly rechecked by EvalPlanQual testing. * * While at it, we strip off
theRestrictInfos to produce a list of plain * expressions.
***************
*** 836,843 **** if (predicate_implied_by(clausel, nonlossy_indexquals)) continue;
! if (predicate_implied_by(clausel, best_path->indexinfo->indpred))
! continue; } qpqual = lappend(qpqual, rinfo->clause); }
--- 840,853 ---- if (predicate_implied_by(clausel, nonlossy_indexquals)) continue;
! if (best_path->indexinfo->indpred)
! {
! if (baserelid != root->parse->resultRelation &&
! !list_member_int(root->parse->rowMarks, baserelid))
! if (predicate_implied_by(clausel,
! best_path->indexinfo->indpred))
! continue;
! } } qpqual = lappend(qpqual, rinfo->clause); }
***************
*** 920,927 **** * but are logically implied by, the index quals; so we also try a * predicate_implied_by()
checkto see if we can discard quals that way. * (predicate_implied_by assumes its first input contains only
immutable
! * functions, so we have to check that.) We can also discard quals that
! * are implied by a partial index's predicate. * * XXX For the moment, we only consider partial index
predicatesin the * simple single-index-scan case. Is it worth trying to be smart about
--- 930,941 ---- * but are logically implied by, the index quals; so we also try a * predicate_implied_by()
checkto see if we can discard quals that way. * (predicate_implied_by assumes its first input contains only
immutable
! * functions, so we have to check that.)
! *
! * We can also discard quals that are implied by a partial index's
! * predicate, but only in a plain SELECT; when scanning a target relation
! * of UPDATE/DELETE/SELECT FOR UPDATE, we must leave such quals in the
! * plan so that they'll be properly rechecked by EvalPlanQual testing. * * XXX For the moment, we only
considerpartial index predicates in the * simple single-index-scan case. Is it worth trying to be smart about
***************
*** 945,952 **** { IndexPath *ipath = (IndexPath *) best_path->bitmapqual;
! if (predicate_implied_by(clausel, ipath->indexinfo->indpred))
! continue; } } qpqual = lappend(qpqual, clause);
--- 959,972 ---- { IndexPath *ipath = (IndexPath *) best_path->bitmapqual;
! if (ipath->indexinfo->indpred)
! {
! if (baserelid != root->parse->resultRelation &&
! !list_member_int(root->parse->rowMarks, baserelid))
! if (predicate_implied_by(clausel,
! ipath->indexinfo->indpred))
! continue;
! } } } qpqual = lappend(qpqual, clause);
***************
*** 1282,1288 **** * join quals; failing to prove that doesn't result in an incorrect * plan. It is
theright way to proceed because adding more quals to * the stuff we got from the original query would just
makeit harder
! * to detect duplication. */ BitmapHeapPath *innerpath = (BitmapHeapPath *)
best_path->innerjoinpath;
--- 1302,1310 ---- * join quals; failing to prove that doesn't result in an incorrect * plan. It is
theright way to proceed because adding more quals to * the stuff we got from the original query would just
makeit harder
! * to detect duplication. (Also, to change this we'd have to be
! * wary of UPDATE/DELETE/SELECT FOR UPDATE target relations; see
! * notes above about EvalPlanQual.) */ BitmapHeapPath *innerpath = (BitmapHeapPath *)
best_path->innerjoinpath;