deadlock - retrying the transaction

Hi,
The bigger my C# web-application gets, the more places I need to put in the
tedious retrying block of code to make sure operations that can run into
database deadlocks are re-run (retried) 3-4 times and give up if after that
it's still in deadlock. I'm very sure that many experienced people out
there already deal with this issue somehow. Is there an alternative to it?
Thanks for your comments and suggestions.
Most deadlocks are due to poor indexing and inconsistent updating in the
order of the tables. You may want to concentrate more on the database
schema and code vs. the C# code.
Andrew J. Kelly SQL MVP
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:%230DDr25yFHA.1040@.TK2MSFTNGP14.phx.gbl...
> Hi,
> The bigger my C# web-application gets, the more places I need to put in
> the
> tedious retrying block of code to make sure operations that can run into
> database deadlocks are re-run (retried) 3-4 times and give up if after
> that
> it's still in deadlock. I'm very sure that many experienced people out
> there already deal with this issue somehow. Is there an alternative to it?
> Thanks for your comments and suggestions.
>
>
|||Thanks for the advice. I understand that indexing and update order of the
tables contribute to deadlocks. My question is is it possible to make a
large application deadlock free? If yes, please share the tips with me how
to ensure that; what type of guidelines/disciplines to follow to ensure
tables are always updated in correct order when there are storeprocedures,
triggers, and direct queries can hit the db at the same time.
If deadlock free is not guaranteed, then it sounds to me that we would need
to put retrying blocks into the code - no other way around.
Hope to hear back from you, thanks again.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uTuJKz6yFHA.3892@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Most deadlocks are due to poor indexing and inconsistent updating in the
> order of the tables. You may want to concentrate more on the database
> schema and code vs. the C# code.
> --
> Andrew J. Kelly SQL MVP
>
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:%230DDr25yFHA.1040@.TK2MSFTNGP14.phx.gbl...
it?
>
|||I don't think there is ever a way to make an app deadlock free but you can
certainly do some things to prevent most occurrences of them. Maybe these
will help:
http://www.sql-server-performance.com/deadlocks.asp
http://www.sql-server-performance.co...ql_locking.asp
http://support.microsoft.com/kb/q169960/
http://www.codeproject.com/cs/database/sqldodont.asp
Andrew J. Kelly SQL MVP
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:eG6qrXCzFHA.2556@.TK2MSFTNGP10.phx.gbl...
> Thanks for the advice. I understand that indexing and update order of the
> tables contribute to deadlocks. My question is is it possible to make a
> large application deadlock free? If yes, please share the tips with me
> how
> to ensure that; what type of guidelines/disciplines to follow to ensure
> tables are always updated in correct order when there are storeprocedures,
> triggers, and direct queries can hit the db at the same time.
> If deadlock free is not guaranteed, then it sounds to me that we would
> need
> to put retrying blocks into the code - no other way around.
> Hope to hear back from you, thanks again.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uTuJKz6yFHA.3892@.TK2MSFTNGP12.phx.gbl...
> it?
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u0osvSDzFHA.2652@.TK2MSFTNGP14.phx.gbl...
>I don't think there is ever a way to make an app deadlock free but you can
>certainly do some things to prevent most occurrences of them. ...
Just a note: there is help on the way for deadlocks. Running on SQL Server
2005 with Read Commited Snapshot Isolation should automatically eliminate
the vast majority of deadlocks.
David
|||Why do you say that? It basically stops readers from blocking writers and
visa versa. It does nothing to prevent writers from blocking writers in the
reverse order.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OmRErFFzFHA.1256@.TK2MSFTNGP09.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u0osvSDzFHA.2652@.TK2MSFTNGP14.phx.gbl...
> Just a note: there is help on the way for deadlocks. Running on SQL
> Server 2005 with Read Commited Snapshot Isolation should automatically
> eliminate the vast majority of deadlocks.
> David
>
>
|||If you have more than one connection to the database, then deadlocks can
happen. Deadlocks can occur on resources other than just locks. Threads
and memory are some examples. Sometimes parallelism can cause deadlocks.
These other kinds of deadlocks are rare, and usually occur on servers with
very heavy loads, but they can occur. Therefore, unless you take no pride
in your work, you should take into account that they can occur in your code.
Retry blocks are one possible solution, and can also be used to recover from
optimistic concurrency collisions. Another would be to return an error
message to the user, and let them resubmit the transaction.
Most deadlocks occur because resources aren't updated in the same order.
This problem is amplified by frequent scans due to a lack of indexes. In my
experience, the best way to mitigate the effect of deadlocks is to execute
transactions within stored procedures. If the transaction is enclosed in a
stored procedure, then most often it's a simple matter to fix the problem.
Usually rearranging statements or adding a simple select statement can be
used to alter the order in which locks are obtained. That's a lot more
difficult to do in client code that needs to be redeployed to tens or
hundreds of workstations. There are several other reasons to issue updates
within stored procedures. It's easier to issue set-based operations within
a procedure--just send the changes in temp tables or (frown) block the
changes in a large varchar parameter that can be parsed within the procedure
and then issue set-based statements to commit the changes. Using stored
procedures makes it easier to wait until the last possible instant to start
the transaction. Set-based operations are much faster and more scalable
than row-based operations because triggers fire only once, updates to
indexes can be optimized, and transaction logging is minimized--all of which
can serve to minimize the duration of transactions which will consequently
reduce the probability of deadlocks. If you must use a cursor, then it's
best to cache the results in temp tables or table variables and flush them
using set-based operations. Another technique is to use optimistic
concurrency with rowversioning which is again simpler to accoplish in a
stored procedure. All of the work required to calculate the results to be
committed is done under a READ COMMITTED isolation level with the max
rowversion of each row source cached in local variable and the results
cached in table variables so that all that is left to do after the
transaction is started is to lock the source rows with REPEATABLE READ
verifying at the same time that the max rowversion hasn't changed, to apply
update locks on all rows to be modified or deleted, and finally to issue the
statements that commit the changes.
To summarize: (1) Enclose transaction processing within stored procedures.
(2) Make sure that you obtain locks in the same order in every procedure,
trigger, function, or process. (3) Wait until the last possible instant to
start a transaction. And (4) keep transaction duration as short as possible
by using set-based operations, by caching and flushing, and/or by using
optimistic concurrency.
One other thing: redundancy in a database can increase the probability of
deadlocks. Make sure your database schema conforms at a minimum to
Boyce-Codd normal form. And (This will probably cause Celko to get out his
flame thrower!) use surrogate keys instead of natural keys in DRI
relationships to eliminate the redundancy inherent in natural foreign keys.
A database that is in 5th normal form and that uses surrogate keys correctly
has the additional property that each extrinsic atomic value exist in
exactly one place in the database. Redundancy can thus be completely
eliminated from the database.
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:eG6qrXCzFHA.2556@.TK2MSFTNGP10.phx.gbl...
> Thanks for the advice. I understand that indexing and update order of the
> tables contribute to deadlocks. My question is is it possible to make a
> large application deadlock free? If yes, please share the tips with me
> how
> to ensure that; what type of guidelines/disciplines to follow to ensure
> tables are always updated in correct order when there are storeprocedures,
> triggers, and direct queries can hit the db at the same time.
> If deadlock free is not guaranteed, then it sounds to me that we would
> need
> to put retrying blocks into the code - no other way around.
> Hope to hear back from you, thanks again.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uTuJKz6yFHA.3892@.TK2MSFTNGP12.phx.gbl...
> it?
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ODkckFHzFHA.2796@.TK2MSFTNGP10.phx.gbl...
> Why do you say that? It basically stops readers from blocking writers and
> visa versa. It does nothing to prevent writers from blocking writers in
> the reverse order.
>
Here are my reasons:
1 My feeling is that a majority of deadlocks involve a shared lock will be
directly eliminated by not issuing shared locks. For the purposes of
deadlock elimination, READ CONCURRENT SNAPSHOT isolation is like putting
NOLOCK on every query, which is a common remedial measure for deadlocks.
2 Across the board improvements in concurrency reduce deadlocks
automatically. This is for the same reason that only busy databases
deadlock. Eliminating S locks will cause fewer lock waits for writing
transactions, which will cause them to hold their X locks for less time,
reducing the time in which they would be vulnerable to a deadlock.
3 Most of the remaining deadlock scenarios are pretty simple and can be
considered coding errors or table design errors.
4 READ CONCURRENT SNAPSHOT is very similar to Oracle's Multi-Version Read
Concurrency, and Deadlocks are extremely rare in Oracle.
David
|||I just hate the thought that people will flock to Snapshot Isolation level
because it is perceived to be easier and better than Read committed. Most
developers that I meet using Oracle do not realize what that (or this new
isolation level) means in regards to the data they are returning. Too many
make decisions based on the data returned without regard to the fact the
data may in fact be changing underneath them. I am not saying there wont be
proper times to use this as everything has it's place. But I already see
where too many people think this will solve all their problems
automatically. Anyway enough of this as this is one of those religious war
kind of topics<g>.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OwWPX0NzFHA.2212@.TK2MSFTNGP15.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ODkckFHzFHA.2796@.TK2MSFTNGP10.phx.gbl...
> Here are my reasons:
> 1 My feeling is that a majority of deadlocks involve a shared lock will be
> directly eliminated by not issuing shared locks. For the purposes of
> deadlock elimination, READ CONCURRENT SNAPSHOT isolation is like putting
> NOLOCK on every query, which is a common remedial measure for deadlocks.
> 2 Across the board improvements in concurrency reduce deadlocks
> automatically. This is for the same reason that only busy databases
> deadlock. Eliminating S locks will cause fewer lock waits for writing
> transactions, which will cause them to hold their X locks for less time,
> reducing the time in which they would be vulnerable to a deadlock.
> 3 Most of the remaining deadlock scenarios are pretty simple and can be
> considered coding errors or table design errors.
> 4 READ CONCURRENT SNAPSHOT is very similar to Oracle's Multi-Version Read
> Concurrency, and Deadlocks are extremely rare in Oracle.
>
> David
>
|||How to implement set-based operations? They are inherent in every SQL
database. Perhaps you misunderstand what I mean by set-based and row-based
operations. A set-based INSERT, UPDATE or DELETE statement may affect
multple rows, whereas with row-based processing, a separate INSERT, UPDATE
or DELETE statement is issued for each affected row.
There are a plethora of articles, books, and courses available on SQL.
Since cursors can be used and misused in many different ways (not to mention
that there are several different types of cursors), there isn't a simple
all-encompassing pro/con comparison that can be made. There are times
(albeit very rare) when a cursor will outperform a similar set-based
query--sometimes even by several orders of magnitude. Most of the time,
however, the reverse is true.
These facts are always true:
(1) triggers fire once per statement. If you send 100 INSERT...VALUES
statements, all INSERT triggers on the table will fire 100 times, whereas if
you issue the 100 INSERT...VALUES against a temporary table, and then issue
a single INSERT...SELECT, then the INSERT triggers will only fire once.
(2) updates to indexes can be optimized. If you send 100 INSERT...VALUES
statements, then index maintenance is performed 100 times, which may mean 99
extra logical writes per index. A single INSERT...SELECT will cause each
index to be updated only once, and if several changes are made on the same
index page, then instead of several individual updates to the same index
page, you get a single write to that index page.
(3) transaction logging is minimized. Every statement that executes has a
certain amount of transaction log overhead associated with it, so if you
send 100 INSERT...VALUES statements, then there is 100 times the overhead
than with a single INSERT...SELECT. In addition, since index updates are
optimized to minimize writes to each index, it follows that the number of
writes to the transaction log to record the old and new index entries is
similarly reduced.
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:OiYpj5fzFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Would you be able to give me a pointer to where I can find more
> information
> about set-based operations such as how to implement them for sqlserver
> 2000
> and pro/con comparisons with the row based operations?
> Thank you very much for the detailed guidance.
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:unvwe4HzFHA.1132@.TK2MSFTNGP10.phx.gbl...
> code.
> from
> my
> a
> updates
> within
> procedure
> start
> which
> apply
> the
> to
> possible
> his
> keys.
> correctly
> the
> storeprocedures,
> the
> in
> after
> out
> to
>