Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.2
-
None
Description
The schema is:
create table table0 (pkId integer, pkAttr0 integer, pkAttr1 integer, coAttr0_0 integer, coAttr0_1 varchar(140), primary key(pkAttr0, pkAttr1));
alter table table0 add index table0index_pk(pkAttr0, pkAttr1);
alter table table0 add index table0index_commAttr0(coAttr0_0, coAttr0_1);
insert into table0 (pkId,pkAttr0,pkAttr1,coAttr0_0,coAttr0_1) values ("29","147","177","73609","aWVjQJkyE5");
Then execution queries below:
session1 > begin;
session1 > select * from table0 where pkAttr1=177 for update; --acquire exclusive lock on the record
session2 > select pkAttr0 , pkAttr1 , coAttr0_0 , coAttr0_1 from table0 where pkAttr1 = 177 lock in share mode; – execute without blocking
session2 > select * from table0 where pkAttr1 = 177 lock in share mode; – select the same record, but blocked
Since session1 acquire a lock on the record(pkAttr1=177), both of select in session2 should be blocked(because they use lock in share mode), but the first one is not blocked.
We explain the plans of queries in session2 and find that the first one uses index but the second one does not use index. Maybe LOCK IN SHARE MODE does not lock the index entry correctly.