SQL Server - Parallel Execution - Asked By Mayank Tripathi on 30-Apr-13 06:21 AM

Hi All,

I am searching for a suitable and reasonable answer to one of my question on parallel execution. Please help me on this.

I have 2 jobs in SQL Server 2008, both the jobs deletes records from table (say tableA).
Condition of Job 1 is -- Delete all records which are older than 15 days.
Condition for Job 2 is-- Delete all those records which satisfy the specific condition.

So there is possibility that both the job can go for deleting same record at any point of time. So in this case will my job get failed or it get go in deadlock or blocking will occur?

Please suggest.
Robbe Morris replied to Mayank Tripathi on 30-Apr-13 09:08 AM
Blocking will likely occur but deadlock is unlikely.  When the WHERE clause grabs a record, a lock is usually placed on it requiring all other updates, deletes, and sometimes even SELECTs (based on index settings) to wait until the operation completes for that record.

The job will not fail specifically because two (or more) sql statements are attempting to access the record.