View Single Post
  #1 (permalink)  
Old May 19th, 2005, 01:20 AM
mike_abc mike_abc is offline
Authorized User
Points: 173, Level: 3
Points: 173, Level: 3 Points: 173, Level: 3 Points: 173, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Dec 2004
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default SELECT with READPAST doesn't work !

Hi all,

I've written a multiuser software in VB.NET using ADO.NET and MSSQL Server 2000.

User #1 performs a
SELECT ... FROM Table1 WITH (ROWLOCK)... ,

the connection of User #1 being

cnx = New SqlConnection(SetConString(10))
cnx.Open()
trx = cnx.BeginTransaction(IsolationLevel.Serializable)

da = New SqlDataAdapter(Sca, cnx)
da.SelectCommand.Transaction = trx
da.Fill(ds, "TabDetails")
...

At the same time, User #2 does a
SELECT ... FROM Table1 WITH (READPAST) ....the connection of User #2 being

[blue]cnx = New SqlConnection(SetConString(10))
cnx.Open()
trx = cnx.BeginTransaction(IsolationLevel.ReadCommitted)

da = New SqlDataAdapter(sSQL, cnx)
da.SelectCommand.Transaction = trx
da.Fill(ds, "TabSursa")
...
cnx, trx being the connection and transaction, respectively.

Connection of User #1 is open a long time, much longer than that of User #2.

The problem : Row locking does work, but not READPAST.

User #2 still sees the record (row) that User #1 has accessed, although the row is locked (I tried to access it and it doesn't work, which is what SQL Server should do), and although the SELECT of User #2 has the READPAST hint.

???

Mike
Reply With Quote