CODEWITHSUNDEEP
sql-serversql-server-2005ssis
Solo
Solo

Reputation: 46

How to avoid deadlock while updating table with two different updates statements on same table in SQL server

I am using update statement in a script task of SSIS which updates a particular row of a table. This process happens in a loop for multiple rows.

Sometimes it happens that another application also fires an update on that same table for a different set of rows. For this application I'm getting deadlock exception on lock.

How can I avoid this situation? I want both updates to work at same time as the row sets being updated are different.

Is the anything to lock only that row which is getting updated?

Regards,
Solo

Upvotes: 2

Views: 3415

Answers (1)

paparazzo
paparazzo

Reputation: 45096

From the MSDN site with a search on TSQL, UPDATE RowLock. There are also PageLock and TableLocks. You can also use transactions if you need to update more than one table. On your reads you may want to use (NOLOCK) if dirty reads are OK (the up side is no read locks). 

    update Production.Location with (ROWLOCK)
    set CostRate = 100.00
    where LocationID = 1

Upvotes: 1

Related Questions