we have a sybase ASE 15 DB and something weird happened last week, it looks like a select on a newly inserted row maybe missed right after the insert
our table uses start/end timestamp for milestoning; an example is
id number,
name varchar(32),
start timestamp,
end timestamp;
an "update" would be an update on existing end; and an insert of the new record; for example
id name start end
1 alice 2010-01-01 00:00:00 3000-01-01 00:00:00
on update will become
id name start end
1 alice 2010-01-01 00:00:00 2010-01-01 08:00:00
1 bob 2010-01-01 08:00:00 3000-01-01 00:00:00
and both update and insert is done in the same transaction
now with row locking and index on (id, start, end), when selecting using isolation level 1, with select query:
select * from table where id=1 and start<=getdate() and end>getdate()
ideally when select is run during update, it should block until commit and return the latest row.
However in our case we saw the blocking, but no rows returned!
This means to me that when the select is run, it sees the update but not the insert; is that possible?
is it possible somehow on commit, the new row is inserted but the index is not updated therefore the select did not see the new row?
Thanks
