Thread: Resolve Locking
12-11-04, 08:16 #1Registered User
- Join Date
- Nov 2004
Unanswered: Resolve Locking
i've a SQL2000 server and SQLserver CE installed, also i've about 100 reps having a palm with them, entering a data through a developed software, then make a daily connection to make synchronization and send their data to the server.
most of thes reps make the synchronization at the same time each day, so most of them get the error message of Deadlock on a resource of process ID...
how can i resolve this issue and what is the most appropriate type of locking i can use to prevent this issue?
thanks in advance
Last edited by mahamemo2000; 12-11-04 at 08:20.
12-12-04, 12:51 #2Registered User
- Join Date
- Jul 2003
- San Antonio, TX
Carefully selecting transaction isolation level is important here. But what's more obvious is the presence of reverse object reference that is the main cause of deadlocks. You need to analyze data access order and see where you introduce something like this:
User1 holds a lock on TableA and tries to access (place a lock) on TableB
User2 holds a lock on TableB and tries to access (place a lock) on TableA
If changing data access order is not feasible, you need to introduce a more tight transactional control and take full advantage of atomicity, which will take care of deadlock situations."The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."