waitfor problem in SQL Server

while 1 = 1     
  begin         
   waitfor time @timeToRun         
   begin             
     /*delete 开发者_StackOverflow社区some records from table X*/        
   end     
  end 

In the codes above, will the SQL server lock the table X during the wait? I would like to insert records into table X during this wait time. Is it possible?

---

All write operations acquire X locks on the rows being updated (deleted) and all these X locks will be hold until the transaction commits. Every statement creates an implicit transaction that commits automatically at the end of the statement, if no transaction is explicitly specified.

So the answer to your question depends whether you call this in a context of an existing transaction or not. If not, then (assuming you do not start a transaction in the inner begin... end block and leave the transaction open) then no lock will be held. If the code is run in a the context of an existing transaction (eg. a TransactionScope in the client started automatically by the WCF service behavior) then any lock placed by the delete will be hold while you wait, until the transaction is committed..

---

Two part question:
1) In the codes above, will the SQL server lock the table X during the wait?
No. It may lock the rows, but not the table.

2) I would like to insert records into table X during this wait time. Is it possible?
Yes, but they will be locked until your commit

Note: you will want to wrap any work you are doing in a transaction with a BEGIN/COMMIT block. This will avoid the locking issue entirely.