rowlock and updlock difference

Question

rowlock and updlock difference and when there use

Answer 1

Please refer to the BOL for table hints.....

By default SQL Server obtains ROWLOCK so I see no reason to use ROWLOCK hint

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

ROWLOCK is the default level of lock granularity. But if you run a query
that needs to access the better part of a big table, SQL Server may decide
to escalate the lock to table level, since this require less resources.
Unfortunately, this can cause the entire table to be inaccessible, which is
not always desirable. In this case you can use ROWLOCK to prevent this. But
to be honest, the few times I've tried it, I have not been successful. So,
ROWLOCK is not a very useful hint at all in my experience.

UPDLOCK on the other hand, is a very useful hint. Consider this:

[/sql]
DECLARE @nextid int
BEGIN TRANSACTION

SELECT @nextid = coalesce(MAX(id), 0) + 1 FROM tbl WITH (SERIALIZABLE)

INSERT tbl (id, col1, col2, ....)
   VALUES (@nextid, @val1, @val2, ...)

COMMIT TRANSACTION
[/sql]

The idea here is that rather than using IDENTITY, we roll our own id scheme.
We use the SERIALIZABLE isolation level, to prevent that two users get the
same id.

However, the above does not work. It does prevent two users from getting the
same id, but at the price of a deadlock, because both holds a lock that
prevents the other to insert. If you add UPDLOCK, this does not happen.
Update locks are read locks, but in difference to regular read locks, only
process at a time can have an Update lock on a resource, so the process that
comes as #2, will be blocked on the SELECT statement.

Note: In fact you should not have SERIALIZABLE at all for a scheme like the
one above. Theoretically it is sound, but in practice this is prone to
deadlocks. UPDLOCK alone is the same as REPEATABLE READ which is good enough
in most cases.

Overall, you should use UPDLOCK when you read a value that you plan to
update later in the same transaction to prevent the value from changing.

---

Erland Sommarskog, SQL Server MVP, [e-mail] Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Answer 3

Hi ErLand,

When i try to lock single row for  table of low volume data with (updlock) its work's fine ,

But locking single row with (updlock) for table of high volume data( i.e 12k rows) an exclusive lock is set on table,

Is there a way to get rid for this by changing any db setting? 

 

Answer 4

When i try to lock single row for  table of low volume data with (updlock) its work's fine ,

But locking single row with (updlock) for table of high volume data( i.e 12k rows) an exclusive lock is set on table,

Is there a way to get rid for this by changing any db setting? 

I'm not really sure that I follow. I just tested a query on a non-indexed column in a table that has 350000 rows, and the query hit 9000 rows. The query used UPDLOCK, and indeed I got row locks.

But if your query hits the better of the rows in the table, you may see lock escalation to table level, but not for a single-row query.

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Answer 5

Hi Erland,

There was no index on table which i was trying to (updlock) after creating PK index it is also

woking fine.

Thanks!

Answer 6

That would still fail if it was the first record on the table because you could have 2 or more trying to insert against the table and the first select would return the same value if they all run before the first insert on either of them. The best way to do this is have a separate table which holds values or have a parent table record which has to be locked first (using a select with (updlock) against the parent)...you need at least one row somewhere for the select to lock and be sure...SQL Server may escalate the lock to table level if the select with updlock returns no rows...I am not sure on that?

But to be absolutely safe (and also have a solution with a cross platform technique) you need a row to lock against.