T-sql Compound Statement Causes The Deadlock, Any Idea Why?

I'm in the process of learning a more complex SQL Server 2008 techniques so I apologize in advance if I ask a too obvious question. I have the following table created as such: CREA

Solution 1:

You either need to hold more locks or fewer.

The easiest answer is to go either NOLOCK (best performance) or TABLOCKX (consistency without having to think).

If you cannot use with (nolock) because of consistency requirements, you can add with (tablockx). This will effectively mean that only one thread can execute like statements at a time - there will be no concurrency.

The alternative is to analyse your requirements in a lot more detail, which cannot be done without an understanding why you are updating the table, what the data is for etc.

For example, does this statement really need to be in the transaction? It smells like housekeeping:

DELETEFROM [dbo].[t_Log_2] 
  WHERE [idtm]<'2011-03-12 08:41:57';

If you take that out of the transaction, and put it in a separate batch, you may find the problems go away.