Tom,

I've had a bug in my code due to using a min with select for update. Now I'm using rownum=1 (which as you have mentioned selects 1 row), which has fixed my problem.

However please could you explain the following to me:

CREATE TABLE t (id VARCHAR2(2) NOT NULL, status VARCHAR2(2));
ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);
CREATE INDEX t_idx ON t (status );

insert into t (id) values('a1');
insert into t (id) values('a2');
insert into t (id) values('a3');

In session 1:
select id from t
where id=(SELECT MIN(id)FROM t WHERE status IS NULL)
for update;

ID
--
a1

Now repeat the same select for update in another session (session2)

session 1:
UPDATE t SET status = 'X' WHERE id = 'a1';
commit;

session2 returns:
ID
--
a1

Which is incorrect as t.status is now not null for this row???

Using rownum=1 fixes this
As you have already mentioned min() does not point to a single row, so dont use it, but why does the select for update get back the wrong row, I thought that the query is re-executed once the row is no longer locked?

does not happen for me -- are you sure you COMMITED the inserts?  (eg: is your UPDATE actually updating anything!!!!)

ops$tkyte@ORA9IR2> CREATE TABLE t        (id   VARCHAR2(2) NOT NULL,  status    VARCHAR2(2));
 
Table created.
 
ops$tkyte@ORA9IR2> ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);
 
Table altered.
 
ops$tkyte@ORA9IR2> CREATE INDEX t_idx ON t (status  );
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t (id) values('a1');
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t (id) values('a2');
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t (id) values('a3');
 
1 row created.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select id from t
  2  where  id=(SELECT MIN(id)FROM t WHERE status IS NULL)
  3  for update;
 
ID
--
a1
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2     pragma autonomous_transaction;
  3  begin
  4          update t set status = 'x';
  5      commit;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
 
 
ops$tkyte@ORA9IR2>


<b>if you run that block of code in your database, what do you see?</b>
 

I ran the block of code and got the same error as you.

I think I wasnt clear enough with my example, ill try again. I've 2 sqlplus sessions with prompts "session1>" and "session2>" respectively:

session1>CREATE TABLE t (id VARCHAR2(2) NOT NULL, status VARCHAR2(2));

Table created.

session1>ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id) ;

Table altered.

session1>CREATE INDEX t_idx ON t (status );

Index created.

session1>insert into t (id) values('a1');

1 row created.

session1>insert into t (id) values('a2');

1 row created.

session1>insert into t (id) values('a3');

1 row created.

session1>commit;

Commit complete.

session1>select * from t;

ID ST
-- --
a1
a2
a3

3 rows selected.

--check from session2:
session2>select * from t;

ID ST
-- --
a1
a2
a3

3 rows selected.


--Now we have our baseline. Table t with 3 commited rows, lets start our transactions:

session1>select id from t
2 where id=(SELECT MIN(id)FROM t WHERE status IS NULL)
3 for update;

ID
--
a1

1 row selected.

session2>select id from t
2 where id=(SELECT MIN(id)FROM t WHERE status IS NULL)
3 for update;

--session2 waits as expected

session1>UPDATE t SET status = 'X' WHERE id = 'a1' ;

1 row updated.

session1>select * from t;

ID ST
-- --
a1 X
a2
a3

3 rows selected.

session1>commit;

Commit complete.

session2> --now the hung session returns:
ID
--
a1

1 row selected.

session2>select * from t;

ID ST
-- --
a1 X
a2
a3
--proves that the wrong row has been returned

--also
session2>select id from t
2 where id=(SELECT MIN(id)FROM t WHERE status IS NULL);

ID
--
a2

1 row selected.
--shows what session2 should have returned.

Hope this clears thing up.

As I've mentioned when I use :
select id from t where id =(SELECT id FROM t WHERE status IS NULL and rownum=1); everything is OK, but what is going on with MIN()?

Thanks

Yep, I added "and status is null" to the outer query and it worked!. I also tried adding status to the cols being returned i.e select id,status from t .......for update
and that works too. I'm now totally baffled as to what is going on.

I'm off to read your articles on write consistency

Cheers

Because the subquery does not care about locking, it might return a record that's already locked by other user. And because the query tries to lock the record, it will have to wait till the record is unlocked. My process hung sometime because of this.