Menu
If I try to delete a brunch of data from a table. say
DELETE FROM myTable Where CreationDate < GetDate()
that takes hours to be deleted, will this table be locked and no new insert can happened?
This table doesn't self reference itself. I would assume I can still insert new data while it is deleting. Will the delete sql uses an exclusive lock that prevents all access to the table?
Thanks
991
DELETE uses a row lock while executing, which means each row in the table is locked for deletion. Once DELETE is executed, a table can still contain empty data pages.
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.
Locks are held on SQL Server resources, such as rows read or modified during a transaction, to prevent concurrent use of resources by different transactions. For example, if an exclusive (X) lock is held on a row within a table by a transaction, no other transaction can modify that row until the lock is released.
When you execute an INSERT, UPDATE, or DELETE statement, the database server uses exclusive locks. An exclusive lock means that no other users can update or delete the item until the database server removes the lock.
You can batch them to prevent it from locking the whole table:
WHILE 1 = 1
BEGIN
DELETE TOP 10000 FROM myTable WHERE CreationDate < GetDate()
IF @@ROWCOUNT = 0
BREAK
END
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
