Dead Lock

Question

Dead Lock

Loner

SSC-Insane

Points: 21279
More actions
May 17, 2007 at 10:20 am

#178987

I have a Product table. Out company has three subsidiaries - US, Canada and England. Each country will insert and update the Product table anytime. They can do it anytime they want and sometimes all three countries insert and update the Product table at the same time. It created a 'DEAD LOCK'.
Product table (ProductID INT IDENTITY(1,1) NOT NULL,
ProductCode VARCHAR(30) NOT NULL,
ProductName VARCHAR(100) NOT NULL)
There are US_product table, Canada_product table, England_Product table. As soon as the job put data in US_product, a trigger in US_product will fire to put data in the global Product table. The same as Canada and England. In order to solve the problem.
Someone suggested to do this.
update product
set productName = 'OK'
from England_Product (nolock)
where source = 'Eng'
But I did not feel comfortable to put 'NOLOCK' in UPDATE statement.
Does anyone have good idea to fix the deadlock problem?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Nolock has no effect in an update statement. It only affects read locks.

Could you post the trigger's code and, if possible the deadlock graph (obtained either using profiler, or from the error log if traceflag 1204 is on)?

Also the structure of the tables and any indexes would be useful.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Loner SSC-Insane Points: 21279 More actions · Answer 2

We use SQL Server 2005.

In order to update TableA from different sources at the same time. If NOLOCK can only work on SELECT, can we use ROWLOCK.

UPDATE TableA

SET Field1 = 'abc'

FROM TableA a WITH (ROWLOCK)

INNER JOIN TableB b ON a.parent = b.parent

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

That query should be fine, and there should be no need for locking hints.

Please can you post the enire trigger code and the schema of TableA?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Anders Pedersen SSChampion Points: 11410 More actions · Answer 4

Do you have a trigger on the global table to save out to the different location tables? That is the only way that (with the information given) that there could be a dead lock. Canada writing to the global, and England writing to the global, even if same record, should not create a dead lock. Timeout possibly if it takes too long, but not a dead lock.