Insert, Delete locking
-
We are runnning processes that do a considerable amount of DB access. We've got "with (ROWLOCK)" specified on all updates and "with (NOLOCK)" specified on all selects. But that doesn't help us with inserts and deletes. These are still causing major locking issues.
So, is there any way to fix this?
-
You can add the "with(ROWLOCK)" to the inserts and deletes. I always add this to UPDATES, INSERTS and DELETES and the "with(NOLOCK)" to SELECT statements. Also I have noticed in my testing that sometime SQL Server does not honour the with(ROWLOCK), but I'm not sure under what conditions it makes this decision.
-
are your inserts and updates wrapped in transaction blocks with a suitable isolation level ?
cheers
dbgeezer
-
Yanno, i tried this once before and query analyzer yelled at me. Just tried it again and it appears to work fine.
As far as the other response, we've tried using begin tran / commit tran / rollback tran but that just causes bigger problems. We've also tried: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; but ran into problems with that, too.
-
I would allways go down the transaction route but this would depend on the level of expertise in your situation.
If you explain in more detail what your application does and what errors you are getting it will be easier for other people to suggest solutions.
The good thing about transactions is that they are designed to stop the problems you seem to be describing - it may be that at the minute you are too busy looking for a solution to your immediate problem. There may in fact be a fundamental design flaw in your database that is causing the problems ...
Post some errors ...
cheers
dbgeezer
-
Basic setup: 2 applications that take XML files and load them into a working table.
4 applications that each take "ownership" of a specified number of data objects (bills) and process them. With these applications we get a lot of:
Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction SQL: Update tblStatus WITH (ROWLOCK) Set OWNER_ID = 'CENTRAL_1', OWNER_DATE = '03/30/2004 13:55' Where BILL_HEADER_ID in ( Select Top 500 BILL_HEADER_ID from tblStatus WITH (ROWLOCK) Where (PROCESS_STAGE = 'RECEIVE_READY' or PROCESS_STAGE = 'PRE_PRICE_READY' or PROCESS_STAGE = 'POST_PRICE_READY' or PROCESS_STAGE = 'PPO_READY') and (OWNER_ID is NULL or OWNER_ID = ''))
2 applications that take ownership of the processed bills (from above), move them to the "permanent" tables, perform validation, generate return XML files and delete the bills from the working tables. With these we're getting errors:
Timeout expired SQL: insert into tblBill_Item select * from tblBill_Item_Work where bill_header_id in (select bill_header_id from tblSTATUS with (ROWLOCK) where owner_id = 'CENTRAL_3')
Transaction (Process ID 71) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction SQL: Delete from tblBill_Item_Work where bill_header_id = '0311'
tables are far from normailzed and that won't be changing. tblStatus has an index on all 3 fields. tblbill_header bill_header_id is the primary key. tblbill_item (and work) has an index on bill_header_id
-
had another think about this and i would suggest rethinking your solution.
getting the top 500 rows in the database will mean that the 4 apps getting them are going to get the same rows some of the time, depending on how frequently rows are added.
i can't think of an elegant way of fixing your problem without making major changes to the way you seem to be working ....
cheers
dbgeezer
-
Make The ISOLATION LEVEL Read committed (SQL Server default level).
and make (No Lock) in Select
and don't supply any Lock hint in Update, insert and Delete (SQL Server will make the default Good locking Choice here
Notes
1- the higher the ISOLATION LEVEL is, the statement (with NOloCK) will not work ..
2- you have to adjust your Selection in Transaction (and make transaction more smaller in code) .. because Transaction make all resources you work with is unavailable to other users (make Locking)
I hope this help u
Alamir Mohamed
Alamir_mohamed@yahoo.com -
Steve, I hadn't heard that before. We actually do have another solution that will get rid of that problem as well as the deadlocking on tblStatus, we just need to get it past management. Your comments just give us more ammo (and possibly explain a few other errors we've been seeing...).
Alamir, not supplying the lock hint leads to *MAJOR* contention issues on our system.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply