CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008
Vinod
Vinod

Reputation: 32861

Handle Lock Manually in SQL Server?

I am new to SQL Server, but am having a fair knowledge of simple things like select/update/delete and other transaction. I am facing a dead lock scenario in my application. I have understood the scenario as many threads are parallel trying to run a set of update operations. Its is not a single update but a set of update operations.

I have understood that this cannot be avoided in my application as many people want to do a update simultaneously. So I want to have a manual lock system. First the thread 1 should check if the manual lock is available and then start the transaction. Mean while if the second thread requests for the lock it should be busy and hence the second thread should wait. Once the first is completed the second should acquire the lock and start with the transaction.

This is just a logic i have thought about. But I do not have any idea of how to do this in SQL Server. Are there any examples which can help me. Please let me know if you can give me some sample sql scripts or links that will be helpful for me. Thank you for your time and help.

Upvotes: 0

Views: 2933

Answers (3)

gbn
gbn

Reputation: 432230

You probably mean "semaphore". That is, something to serialise execution of the DML to only one process can run at a time.

This is native in SQL Server using sp_getapplock

You can configure 2nd processes to wait or fail when they call sp_getapplock, and also it can be self-cancelling in "transaction" mode.

Upvotes: 2

Chris Bednarski
Chris Bednarski

Reputation: 3414

You will still most likely end up in the same scenario. Having a dead lock based around your tailor made locks. SQL Server internally implements a very robust locking mechanism. You should use it.

The problem you're having is that resources (tables, indexes, etc.) are accessed (or modified) in a conflicting order by different transactions/threads.

If you create your own locking mechanism, you may end up with a dead lock just the same. Example:

  1. Thread 1 creates a lock on Customer record
  2. Thread 2 creates a lock on Order record
  3. Thread 1 attempts to create a lock on Order record (but cannot proceed due to step 2)
  4. Thread 2 attempts to create a lock on Customer record (but cannot proceed due to step 3)

Voila ... deadlock

The solution is to refactor the way resources are accessed, so records are always accessed in the same order and the problem will go away.

  1. Thread 1 creates a lock on Customer record
  2. Thread 2 attempts to create a lock on Customer record (but cannot proceed due to step 1)
  3. Thread 1 creates a lock on Order record
  4. Thread 1 completes transaction and unlocks both Order and Customer records
  5. Thread 2 creates a lock on Customer record
  6. Thread 2 creates a lock on Order record

Also, have a look here to read how locking can happen on a single table.

Upvotes: 2

Will
Will

Reputation: 900

You manual Lock system sounds interesting but you need to aware that it will sacrifice concurrency, which is quite important for many OLTP application.

Advance db like Oracle and SQL server is quite good in avoiding dead lock and give you the tool to resolve dead lock, which help you just kill the session that cause the dead lock and let the other query finish it's job first.

Microsoft Has documentation which can be find here. http://support.microsoft.com/kb/832524

Beside, there are many other reasons that could lead to deadlock. You can find some example here. how to solve deadlock problem?

Upvotes: 0

Related Questions