March 29, 2011 at 10:32 pm
Hi everybody, my name is bub, I am new on sqlservercentral and happy to join this community.
I am asked to solve some deadlock-problems for an existing product. As with all other people's products, the code is hopeless spaghetti, not even a level 1 data-consistency. The product is simply asking for deadlocks, I want to make a quick and simple solution in a first phase to avoid the deadlocks.
Strictly speaking, I don't want 2 transactions to run at the same time. I know this sounds awfully anti T-SQL but it will be the start to slowly rewriting existing transactions and getting the tables a bit consistent.
Now the question:
* At the beginning of each transaction, I want to be able to see if other transactions are running and I want to know which transaction. Instead of an hour-glass or deadlock, I want to inform the user that transaction X is running, put this transaction in some kind of queue and execute when other transactions that come before in the queue have been executed.
Example:
Transaction 1 puts value 'transaction 1' as a new row into a Table Queue with flag 'Executed=False' before starting the transaction. It then checks the first row that has value 'Executed=False'. If it is for example 'transaction 2', it will give a message to the user to wait for the end of 'transaction 2 and keeps on checking the first row with 'Executed=False'. If it is 'transaction 1', it will execute the transaction and put the value 'Executed=True' at the end of the transaction
Does this make sense and/or is there an easier way to get this kind of behaviour?
Love from India
bub
March 30, 2011 at 5:55 am
If you really want to lock down access, you could set the transaction level for the connections to serializable. That will block pretty much everything until a given transaction completes. It won't prevent all possible access everywhere, so you may still experience the occasional deadlock, but I suspect it will eliminate most of them.
However, you're going to see tons and tons of blocking, which means really poor performance. Just be prepared. You can make processes wait and have processes go fast at the same time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 31, 2011 at 1:42 am
Why not, cheap and best as they say in India 🙂
A lot of waiting going on as prognosed but no deadlocks so far, not too bad for a fast start, thanks!
bub
March 31, 2011 at 3:27 am
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply