Shulou(Shulou.com)06/01 Report--

One of the benefits of table partitioning: Deadlock can be avoided, partitions are independent of each other, X locks are added to one partition, and contention is not generated on other partitions.

In the project, there are the following Partition Function and Partition Scheme

CREATE PARTITION FUNCTION [funcPartition_int_DataSourceID] (int) AS RANGE LEFT FOR VALUES (1,2,3) CREATE PARTITION SCHEME [schePartition_int_DataSourceID] AS PARTITION [funcPartition_DataSourceID] TO ([PRIMARY], [PRIMARY]) create table dbo.dt_test (. More column definitionDataSourceID int) on [schePartition_int_DataSourceID] (DataSourceID)

If you look at the execution log of ETL, you will sometimes find Deadlock Issue. For the relevant package Troubleshooting, you will find that the root cause where deadlock occurs is: two statements that update the table at the same time generate contention, resulting in deadlock. Careful with the check code, both updated queries use Partition Column (DataSourceID) as the filter condition. I speculate that the two DataSourceID may be in the same Partition.

1, verify boundary value

Select prv.function_id,pf.name,pf.boundary_value_on_right,prv.value as BoundaryValuefrom sys.partition_range_values prvinner join sys.partition_functions pf on prv.function_id=pf.function_idwhere pf.name='funcPartition_int_DataSourceID'

The value of BoundaryValue is less than the maximum value of the current DataSourceID, and the two DataSourceID that produce the contention are in the rightmost partition.

With the increase of project data and staff turnover, the lack of a reasonable management plan, resulting in additional DataSourceID are assigned to the same partition.

2. Create Job and partition automatically

As a best practice, if a partition is non-empty, then split range will lead to data movement, which can be a very IO-consuming process. To avoid extensive data movement, it is best to reserve an empty partition and split range from the empty partition each time.

Use db_studygodeclare @ CurrentMaxBoundaryValue intdeclare @ ExistingMaxDataSourceID intdeclare @ BoudaryValue intselect @ ExistingMaxDataSourceID = max (dds.DataSourceID) from dbo.dt_DataSource dds with (nolock) select @ CurrentMaxBoundaryValue= max (cast (prv.value as int)) from sys.partition_functions pf inner join sys.partition_range_values prv on pf.function_id=prv.function_idwhere pf.name='funcPartition_int_DataSourceID'-- add new boundary valueif @ CurrentMaxBoundaryValue

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.