difference between UPDLOCK and FOR UPDATE

given the following code:

Set conn = CreateObject("ADODB.Connection")
Set RS = CreateObject("ADODB.Recordset")
conn.BeginTrans
...
//This is the line it is about
RS.Open "SELECT a,b FROM c FOR UPDATE", conn, adOpenDynamic, adLockPessimistic
...
RS.Close
conn.CommitTrans
conn.Close

What is the difference when I use:

RS.Open "SELECT a,b FROM c (UPDLOCK)", conn, adOpenDynamic, adLockPessimistic

UPDATE:

Right now we use FOR UPDATE but it looks like the table is not locked. The SQL Profiler shows us this:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

declare @p1 int  set @p1=NULL  declare @p3 int  set @p3=229378  declare @p4 int  set @p4=163842  declare @p5 int  set @p5=NULL  exec sp_cursoropen @p1 output,N'SELECT a,b FROM c FOR UPDATE',@p3 output,@p4 output,@p5 output  select @p1, @p3, @p4, @p5 

UPDATE c SET a = a + 1 WHERE b = 'Value' 

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

IF @@TRANCOUNT > 0 COMMIT TRAN

The update statement we use:

SQL = "UPDATE c SET a = a + 1 WHERE b= 'Value'"

FOR UPDATE is not valid SQL Server syntax in a regular SQL statment, it is used when you create a cursor.

But you are probably using ADO with CursorLocation adUseServer and then your query actually works because ADO will use sp_cursoropen which accepts the syntax used for cursors.

The default behavior in SQL Server is that cursors can be updated so specifying for update does nothing for you unless you also specify a column list.

Specifying the updlock query hint on a cursor will only do things for you if you are running in a transaction. With updlock the locks is placed when you do fetch next from ... and without updlock the lock is placed when you do update ... where current of, still only if you are in a transaction.

So in your case, using updlock will place locks when you fetch data if you are in a transaction. If you don't use updlock, in a transaction, you will place the locks when you update the data. If no transaction is present there is no difference between the two and you could as well not use any of them.

StackExchange Database Administrators Q#35532, answer score: 6