How to avoid the error in SQL server

Transact-SQL
1

Hi,

I got an below 2 error's,

Error 1:

[SQL
Server Native Client 10.0][SQL Server]Transaction (Process ID 238) was
deadlocked on lock resources with another process and has been chosen as the
deadlock victim. Rerun the transaction.

Native
Error Code: 1205

DELETE
FROM dbo.PriceList_ClassRecord WHERE PriceListGUID in ( Select GUID
from dbo.PriceList_C WHERE GUID in
('3E5E14E5F006EB4AA41F5E6571C2C3AD'))

Error :2

[SQL
Server Native Client 10.0][SQL Server]The DELETE statement conflicted with the
REFERENCE constraint
"FK__PriceList_ClassRecord_PriceList_ClassRecord_". The
conflict occurred in database "test", table "dbo._PriceList_ClassRecord",
column 'PriceListGUID'.

Native
Error Code: 547

ODBC
Status 01000 at SQLExecDirect:

[Microsoft]

[SQL
Server Native Client 10.0][SQL Server]The statement has been terminated.

Native
Error Code: 3621

DELETE
FROM dbo.PriceList_C WHERE GUID in ('3E5E14E5F006EB4AA41F5E6571C2C3AD')

table design
Table: PriceList

GUID - varchar2(36), notnull -- PK
Price - decimal (18,2)

Table : PriceList_ClassRecord

GUID - varchar2(36), notnull -- PK
PricelistGUID- varchar2(36), notnull - "FK_Pricelist_calssRecord_PriceList_classReocrd"(ForeignKEy)

How to avoid this error and i am not able to reproduce this same error. Why this error occured.
Could you give your suggestion . please help me.

2

The error after:

DELETE
FROM dbo.PriceList_ClassRecord WHERE PriceListGUID in 
     ( Select GUID
       from dbo.PriceList_C 
       WHERE GUID in ('3E5E14E5F006EB4AA41F5E6571C2C3AD'))

is a foreign key violation check the foreign key and you will see that the PriceListGUID is used by another table or possibly this one if it points to itself.

The Deadlock issue is a timing issue two command ran at the same time and locked records so neither one could finish. SQL Server detects this issue and picks one process to kill so the other can survive.

3

Can you reproduce the deadlock? If so, use Profiler to set up a trace, enabling Deadlock Graph. Then run the queries that cause the deadlock and look at the graph. It will tell you why.

The second error is because there is a Foreign Key referring to the row(s) you are trying to delete. That violates the integrity constraints. the easy way to do this is to add ON DELETE CASCADE to the table definition as shown here:

table constraints

The other way would be to remove the reference first, then the row referred to. Usually you'd write a proc to do the delete for consistency