handle transaction without deadlock
-
I need to wrap a stored procedure within the transaction and this stored procedure spans through 6 to 7 tables. So, there are high chances of dead lock in our application as we have more updates on tables. What is the best approach to handle transaction here?
-
I need to wrap a stored procedure within the transaction and this stored procedure spans through 6 to 7 tables. So, there are high chances of dead lock in our application as we have more updates on tables. What is the best approach to handle transaction here?
Deadlocks are caused when a thread tries to access a piece of information which is currently held by another thread. Why don't you execute on procedure and keep the rest of the threads on wait. In many ways, a Mutex may help you overcome the deadlock. You can, this way, handle what happens when there are multiple threads using the resources. I would still recommend, that you try to execute "One SQL Procedure" at a time. Keep the rest of the threads (or requests) on hold. https://colinlegg.wordpress.com/2014/05/06/enforcing-mutex-on-a-sql-server-database/[^]
The shit I complain about It's like there ain't a cloud in the sky and it's raining out - Eminem ~! Firewall !~
-
Deadlocks are caused when a thread tries to access a piece of information which is currently held by another thread. Why don't you execute on procedure and keep the rest of the threads on wait. In many ways, a Mutex may help you overcome the deadlock. You can, this way, handle what happens when there are multiple threads using the resources. I would still recommend, that you try to execute "One SQL Procedure" at a time. Keep the rest of the threads (or requests) on hold. https://colinlegg.wordpress.com/2014/05/06/enforcing-mutex-on-a-sql-server-database/[^]
The shit I complain about It's like there ain't a cloud in the sky and it's raining out - Eminem ~! Firewall !~
Afzaal Ahmad Zeeshan wrote:
Deadlocks are caused when a thread tries to access a piece of information which is currently held by another thread.
Almost. A deadlock[^] occurs when two or more competing threads are waiting for each other to release a lock. For example:
- Thread 1 locks X;
- Thread 2 locks Y;
- Thread 1 tries to acquire a lock on Y - waits for thread 2 to release;
- Thread 2 tries to acquire a lock on X - waits for thread 1 to release;
The DBMS can usually detect the deadlock, and will kill the thread which has done the least work. NB: There's nothing wrong with trying to access something locked by another thread, so long as the current thread doesn't currently hold any locks which would prevent that thread from finishing.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
