p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Pro VB Databases (http://p2p.wrox.com/forumdisplay.php?f=81)
-   -   SELECT with READPAST doesn't work ! (http://p2p.wrox.com/showthread.php?t=29486)

mike_abc May 19th, 2005 01:20 AM

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


All times are GMT -4. The time now is 09:52 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.