Ex)
When should I use this statement:
DELETE TOP (@count) FROM ProductInfo WITH (ROWLOCK) WHERE ProductId = @productId_for_del;
And when should be just doing:
DELETE TOP (@count) FROM ProductInfo WHERE ProductId = @productId_for_del;
Table locks. A statement can lock the entire table. Table-level locking systems always lock entire tables. Row-level locking systems can lock entire tables if the WHERE clause of a statement cannot use an index.
UPDLOCK uses an update lock when reading a table instead of a shared lock, and keeps the lock until the end of the statement or transaction.
Deleting all rows by using DELETEDELETE uses a row lock while executing, which means each row in the table is locked for deletion.
When inserting a record into this table, does it lock the whole table? Not by default, but if you use the TABLOCK hint or if you're doing certain kinds of bulk load operations, then yes.
The with (rowlock)
is a hint that instructs the database that it should keep locks on a row scope. That means that the database will avoid escalating locks to block or table scope.
You use the hint when only a single or only a few rows will be affected by the query, to keep the lock from locking rows that will not be deleted by the query. That will let another query read unrelated rows at the same time instead of having to wait for the delete to complete.
If you use it on a query that will delete a lot of rows, it may degrade the performance as the database will try to avoid escalating the locks to a larger scope, even if it would have been more efficient.
Normally you shouldn't need to add such hints to a query, because the database knows what kind of lock to use. It's only in situations where you get performance problems because the database made the wrong decision, that you should add such hints to a query.
Rowlock is a query hint that should be used with caution (as is all query hints).
Omitting it will likely still result in the exact same behaviour and providing it will not guarantee that it will only use a rowlock, it is only a hint afterall. If you do not have a very in depth knowledge of lock contention chances are that the optimizer will pick the best possible locking strategy, and these things are usually best left to the database engine to decide.
ROWLOCK means that SQL will lock only the affected row, and not the entire table or the page in the table where the data is stored when performing the delete. This will only affect other people reading from the table at the same time as your delete is running.
If a table lock is used it will cause all queries to the table to wait until your delete has completed, with a row lock only selects reading the specific rows will be made to wait.
Deleting top N where N is a number of rows will most likely lock the table in any case.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With