difference between UPDLOCK and FOR UPDATE

Question

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'"

Answer 1

In general - there is no difference. Oracle, DB2, MySql uses FOR UPDATE, but in SQL Server you can use the UPDLOCK query. The result is the same - it blocks the selected rows to make the updates for table. But the implemention is a bit different. In Sql Server it sometimes does locking not only on row level, but also on page or even table level. And main - it blocks the results for new selects (so you can have real problems with deadlocks sometimes). Oracle does locks only on row level and they are available for select. As far as I know is not the best practice to use these server features, so try to avoid using them.

Answer 2

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.