October 23, 2009 at 1:12 am
I tried the following, and expected, that if I run two instances of the .exe, then one of them should block, due to the "UPDLOCK".
To my surprise, both instances are running without any blocking.
I looked out for "locktime", but could not find, where I can specify that.
The simple code is given below.
---------------------------------------------------------------------
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim connetionString As String
Dim cnn As SqlConnection
Dim cmd As SqlCommand
Dim sql As String
Dim reader As SqlDataReader
connetionString = "Data Source=Whurray;Initial Catalog=TestDB;User ID=patrudu;Password=pgpatrudu"
sql = "Select * from TestTable1 WITH (UPDLOCK, HOLDLOCK, ROWLOCK) where Id > 0 "
cnn = New SqlConnection(connetionString)
Try
cnn.Open()
cmd = New SqlCommand(sql, cnn)
reader = cmd.ExecuteReader()
While reader.Read()
MsgBox(reader.Item(0) & " - " & reader.Item(1) & " - " & reader.Item(2))
End While
reader.Close()
cmd.Dispose()
cnn.Close()
Catch ex As Exception
MsgBox("Exception : " + ex.Message)
End Try
End Sub
End Class
---------------------------------------------------------------------
Your comments and suggestions are most appreciated.
Regards & Thanks
P G Patrudu.
October 23, 2009 at 1:45 am
Hi All,
I realized, I have to put the select with updlock in a transaction.
Thanks to the members of this site. They have already explained
this problem.
Thanks & Regards
P G Patrudu.
🙂
October 23, 2009 at 2:09 am
Hi All,
The inclusion of Transaction helped updlock to work, but Iam not pretty comfortable with the semantics.
The second instance is held, until the first one completes.
I suspected the HOLDLOCK in the code :
WITH (UPDLOCK, HOLDLOCK, ROWLOCK)
and changed it to : WITH (UPDLOCK, ROWLOCK)
This did not change anything. Still the second instance is starting only after the first instance has completed. :w00t:
I had extensive experience on DB2, and the DB2 Cursor stability is a beauty; It holds the lock as long as you are on the page.
Can I assume that SQL Server is doing the same? If it were to be true, then ROWLOCK is non-significant?
It is sad, that in this era, there is not good documentation for SQL Server, where as IBM explained very clearly in real ink, in 1970's & 80's, what they mean, and DB2 ran & still runs exactly like what they said.
Thanks & Regards
P G Patrudu.
October 23, 2009 at 2:21 am
It is sad, that in this era, there is not good documentation for SQL Server
That is one thing I haven't heard Microsoft being accused of!!
Have you checked UPDLOCK in Books On Line (BOL) for SQL Server 2000... it seems to explain exactly what you are seeing very well.
Edit: Added the entry from BOL just in case you don't have access to it.
UPDLOCK Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it.
October 23, 2009 at 2:23 am
Im not sure what your point now is..
1st post was about sqlserver not blocking , now you seem to take issue with it blocking.
The cause of the block is UPDLOCK , this is all well documented at
http://msdn.microsoft.com/en-us/library/ms187373.aspx.
As for it not being on Paper and ink, your more than welcome to print the entire BOL yourself. Seems like a step backwards to me. And finally SQLServer is not DB2 and DB2 is not SQLServer, a Ferrari is also not a ford.
October 26, 2009 at 5:55 am
Hi All,
Iam not accusing anyone; I dont endorse any product, nor dislike any product. In 1981, when there was only little knowledge about computing in India, I chose Computer Engineering, and loved and continue to love, every piece of software, that I come across.
(I took a course at North Eastern University in ATL, and spent $1000).
We merely remember the good old times, when Regan was the president of USA, and you could get a new job in USA, in say a week. In that way, Mainframes like Unisys/IBM, ring extraordinary good times.
Coming back to SQL, Iam merely as curious as I was 20 years back.
From what I guess, you cannot relinquish an UPDLOCK, once you get it, in a transaction, in SQL Server. You could do it in DB2, by a careful excercise.
Regards
Pilla G Patrudu.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply