Updating values in partition-key columns

For semantically partitioned tables, updating the value in a partition-key column can cause the data row to move from one partition to another.

Adaptive Server updates partition-key columns in deferred mode when a data row must move to another partition. A deferred update is a two-step procedure in which the row is deleted from the original partition and then inserted in the new partition.

Such an operation on data-only-locked tables causes the row ID (RID) to change, and may result in scan anomalies. For example, a table may be created and partitioned by range on column a:

create table test_table (a int) partition by range (a)
	(partition1 <= (1),
	partition2 <= (10))

The table has a single row located in partition2. The partition-key column value is 2. partition1 is empty. Assume the following:

Transaction T1:
	begin tran
	go
	update table set a = 0
	go

Transaction T2:
	select count(*) from table isolation level 1
	go

Updating T1 causes the single row to be deleted from partition2 and inserted into partition1. However, neither the delete nor the insert is committed at this point. Therefore, select count(*) in T2 does not block on the uncommitted insert in partition1. Rather, it blocks on the uncommitted delete in partition2. If T1 commits, T2 does not see the committed delete, and returns a count value of zero (0).

This behavior can be seen in inserts and deletes on data-only-locked tables that do not use partitions. It exists for updates only when the partition-key values are updated such that the row moves from one partition to another. See Chapter 1, “Controlling Physical Data Placement,” in the Performance and Tuning Series: Physical Database Tuning and Chapter 5, “Indexes,” in the Performance and Tuning Series: Locking and Concurrency Control.