Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'"
share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
the above code is exactly what we use. the dots are just other code and lookups. Your answer looks like you didn't read the code provided since i did include a column list and a transaction and am not using a cursor. Or am I missing your point here? –  amaters Feb 27 '13 at 10:28
    
@amaters Have a look with SQL Server Profiler at what is actually executed against SQL Server when you run your code. What is the CursorLocation on your recordset object? –  Mikael Eriksson Feb 27 '13 at 10:45
    
I updated the question, perhaps with this you can help me out? –  amaters Feb 27 '13 at 10:50
1  
@amaters for update does not lock the table. I don't understand why you think you need to lock anything at all. I also don't understand why you are using a recordset. The update statement can be sent with a command object and any locks needed will be taken for you by SQL server whenever they are needed. –  Mikael Eriksson Feb 27 '13 at 17:54
    
Maybe you missed the ... in the code. also there is code before and after the moment this code is called. The flow of the program (which I won't discuss) makes it mandatory that certain records need to be locked with 100% certainty that the row cannot be updated by another statement. –  amaters Feb 28 '13 at 7:45
add comment

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.

share|improve this answer
    
Thanks. You say: 'in general'. I need to know if there is ANY specific difference between the 2 statements. We are using MSSQL only –  amaters Feb 27 '13 at 10:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.