| Maggie 2002-11-07, 2:23 pm |
| Deadlock Error 1205 When update the Updatable Partitioned
Views
Version: SQL Server 2000 with sp2
OS: Winddows 2000 sp3 advance server
I created updatable partitioned views based on 8 base
tables:
create VIEW dbo.INVENTORY
AS
SELECT INVENTORY_200003.*
FROM INVENTORY_200003
UNION ALL
SELECT INVENTORY_200006.*
FROM INVENTORY_200006
UNION ALL
SELECT INVENTORY_200009.*
FROM INVENTORY_200009
UNION ALL
SELECT INVENTORY_200012.*
FROM INVENTORY_200012
UNION ALL
SELECT INVENTORY_200103.*
FROM INVENTORY_200103
UNION ALL
SELECT INVENTORY_200106.*
FROM INVENTORY_200106
UNION ALL
SELECT INVENTORY_200109.*
FROM INVENTORY_200109
UNION ALL
SELECT INVENTORY_200112.*
FROM INVENTORY_200112
I successfully insert records via view to all base
tables. Now, I am try to do some updates, such as,
UPDATE INVENTORY
SET EMP_GROUP = MAX_EMP.EMP_GROUP
FROM INVENTORY
INNER JOIN MAX_EMP ON
INVENTORY.HMO = MAX_EMP.HMO AND
INVENTORY.CIN = MAX_EMP.CIN
WHERE (INVENTORY.EMP_GROUP IS NULL)
However, I got error 1205 Transaction (Process ID %d) was
deadlocked on {%Z} resources with another process and has
been chosen as the deadlock victim. Rerun the transaction.
I did try this update one single table. It works
perfectly. But it doesn't work on updatable partitioned
views. Is this microsoft bug and limitation on
partitioned views? Or what should I do on this?
Thanks.
Maggie
|