cancel
Showing results for 
Search instead for 
Did you mean: 

Sybase 15.7 “select for update” + “readpast” race condition

0 Kudos

I have a simple SQL,

begin tran xxx;
select top 50 * from aTable readpast for update;
commit tran xxx;

And I ran from three threads (each thread starts own transaction). What I have observed is that sometimes two threads are returning the same set of records.

According to Sybase manual, "for update" should have locked the records that I have selected and readpast should have prevented me from reading locked records.

isolation level is 1.

select for update has been enabled sp_configure.

aTable is datarows locking

Any pointer is appreciated.

former_member188958
Active Contributor
0 Kudos

It is a good idea to include the exact version (select @@version) when reporting issues.
Are you currently on the most recent 15.7 rollup?

This transaction looks simple enough that I think it is quite possible that thread A is completing it's select and committing before thread B begins it's select.

Do you see duplicate result sets if you leave the transactions open, or delete the selected rows before committing?

-bret

0 Kudos

Hi Bret, sorry I forgot to add another statement, how do I edit the question? I couldn't find edit button.

I actually deleted the "top 50" records after selecting them.

former_member188958
Active Contributor
0 Kudos

Hi Tong,

I still don't know the exact version of your ASE; knowing the version helps Support filter through bug reports.

I don't know if it is possible to edit your original post, but probably not a good idea anyway as it can make subsequent replies look like gibberish. Just post corrected details as a comment.

What is your client? (isql, jconnect, dbisql, etc.?)
What is the actual transaction?
How are you determining which rows to delete?
How are you determining that another session is selecting some of the same rows?

Cheers,
-bret

0 Kudos

bret.halford I have replied in the answer's section on how to reproduce it reliably and the version of Sybase we tried on. Would you mind to take a look?

0 Kudos

bret.halford I wonder if you have had some time to look into this issue?

Accepted Solutions (0)

Answers (2)

Answers (2)

0 Kudos

Hi Bret,

We have managed to reproduce the problem. If you start two Sybase DB consoles clients, each one running the below script, we can observe that same "valueField" value got processed by both clients.

Note that TABLE doesn't have duplicate records.

Any pointer is appreciated.

Sybase version: Adaptive Server Enterprise/15.7/EBF 28807 SMP SP140 ONE-OFF/P/x86_64/Enterprise Linux/ase157sp140x/4122/64-bit/FBO/Mon Jan 21 04:04:08 2019

while (select count(*) from TABLE) > 0
begin
BEGIN TRANSACTION
declare @valueField varchar(12)
select top 1 @valueField =  valueField from TABLE updlock readpast where priority = 1 order by systemDate for update
print @valueField
delete from TABLE where valueField = @valueField
WAITFOR DELAY '00:00:01'
COMMIT TRANSACTION
end


Cheers,
Tong

sladebe
Active Participant
0 Kudos

Re: readpast should have prevented me from reading locked records

[Comment deleted by the author because it was wrong]

former_member188958
Active Contributor

Hi Ben,

The intention of the READPAST option is that it will allow the scan to skip over exclusively locked records and return rows that match the search criteria that aren't locked. It should not return rows with uncommitted changes, it is not the same as isolation level 0 / Dirty reads.
READPAST is often used for queue processing applications, where you want to grab the next available item to work on without being blocked by items that are currently being worked on.

-bret