This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
This week I worked on a very interesting service request about a deadlock issue when our customer is running 32 threads at the same time inserting data. I saw an important thing that may prevent a deadlock.
- Our customer has two tables with the following structure:
- In our customer code, they create a transaction that insert a new row in the header table and after insert around 1000 rows in the detail table. This operation is running by 32 threads at the same time.
- During the execution we saw some deadlocks because 2 transactions block each other from continuing because each has locked a database resource that the other transaction needs. SQL Server handles deadlocks by terminating and rolling back transactions that were started after the first transaction. In this situation it is a KeyLock.
- We saw that the deadlock is happening when these a transaction needs to lock a resource that the other transaction has in exclusive mode.
- We have two options:
- Retry the operation about the transaction that was killed by SQL Server.
- Create a new Partition Key based on the number of threads running.
My suggestion suggested was to create a partitioned table based on the numbers of threads, for example:
- Create a partition function for the 32 threads, running the following command:
- Create the partition schema based on the function defined:
- For the table header perform two operations:
- First, include the partition key.
- Second, include the PK to have the referential integrity with Detail table.
- In the detail table, include the IdPartition column to maintain the same referential integrity with header table.
But, what is the outcome?
- As we discussed previously, at the moment of the transaction that needs to lock exclusively the PartitionKey of the Primary Key, instead of that all transactions lock the same, every transaction will lock the partition key of the partition, reducing a lot the contention and preventing any deadlock.