SQL Server Performance Forum – Threads Archive

Table Lock on delete

Is there a lock on the table if I were to run: delete from
[table name]
where
[date time field] < @pdate

---

While the delete is happening, there would be some kind of a lock on the table. There is a type of lock for anything you do against a recordset in SQL Server though. ??? What are you looking for specifically? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

from BOL :
SQL 2000 uses locking to ensure transactional integrity and database consistency. Locking prevents users from reading data being changed by other users, and prevents multiple users from changing the same data at the same time.
Although SQL Server enforces locking automatically, you can design applications that are more efficient by understanding and customizing locking in your applications. U can even try running this sp:
sp_lock

---

I am looking to purge records from a table using a stored procedure (job). This purge will take anywhere from 3-5 minutes on average. My concern is the .NET application that runs against this table (inserting, updating mnay records during this same 3-5 minute span) and the potential for timeout issues since the command timeout object is set for 30 seconds.

---

Try to batch deletes, each transaction 5000 rows or something like that.

---

]]>