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 (DOL) tables causes the row ID (RID) to change and can 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 not 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 DOL tables not involving partitions. It exists for updates only when the partition key values are updated such that the row moves from one partition to another. See the Performance and Tuning Guide for more information.
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
