================================================ 
SEARCHDATABASE.COM'S DBA Tips and Tricks 
July 25, 2001 
================================================ 

Welcome to the searchDatabase.com DBA Tips and Tricks newsletter!
Today's tip, "Minimizing deadlocks in SQL Server" can also be viewed
online at: 
http://www.searchDatabase.com/tip/1,289483,sid13_gci757279,00.html

******** Sponsored by Lumigent ***************** 

Stop GUESSING - Use the transaction log to find ANSWERS! 

SQL Server stores a wealth of information in the transaction log. Now
you can access that data with Log Explorer to: Recover column, row,
or table data selectively and online, solve "whodunnit" problems by
examining the log directly, analyze performance and throughput -- And
MORE!!! Get your FREE TRIAL:  http://www.lumigent.com/go/searchdb.htm

************************************************ 

"Minimizing deadlocks in SQL Server" 

By Barrie Sosinsky

Whenever more than one person has access to a database at the same
time, the possibility of a deadlock occurring is high. A deadlock
occurs when a user starts a transaction and locks the records they
are using. Another user wants to access those records and cannot, so
they begin a transaction and lock records in another part of the
database, where the first user needs to access a record as well.
Until the records are released and mutual blockings are cleared, a
deadlock results.

SQL Server has a built in mechanism for detecting and resolving
deadlocks. Once it finds a deadlock situation, SQL Server will assess
the execution cost, and select which transaction will be executed. It
will notify the other processes waiting in the deadlock that an error
(error 1205) has occurred and will let them know they have been
selected as victims in the deadlock situation. If the processes are
blocking one another in a circular type reference, SQL Server will
decide which process will be selected to break the deadlock. The one
with the least overall cost usually wins. When a transaction is
selected as the victim in a deadlock, the process is cancelled and
changes applied are rolled back. The calling application, however,
can usually resend the transaction once the previous locks have been
removed.

To keep deadlock occurrences to a minimum you can make a concerted
effort to keep transactions as short as possible. Avoid user
interaction from inside transactions. Begin a transaction when
required but release it as quickly as possible. Use the same order
when accessing resources. Check for potential circular references. It
is recommended that you use the READ COMMITTED isolation level
whenever possible. It produces fewer locks than higher isolation
levels. Avoid SERIALIZABLE as much as you can. Bind multiple
connections to share the same locking space. Execute the stored
procedure sp_bindsession to keep more than one session in the same
transaction. Although not guaranteed to eliminate deadlocks
completely, these steps should help you keep deadlocks to a minimum
when using SQL Server. 

ABOUT THE AUTHOR
Barrie Sosinsky ([EMAIL PROTECTED]) is president of consulting
company Sosinsky and Associates (Medfield MA). He has written
extensively on a variety of computer topics. His company specializes
in custom software (database and Web related), training and technical
documentation.

SUBMIT YOUR OWN TIP AND WIN A PRIZE
http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz520733_cts520732,00.html
 

******** Sponsored by SyncSort ***************** 

FREE handy multi-tool from Syncsort, the makers of the toolkit of
data management software for achieving cutting-edge data warehouse
performance: SyncSort is a powerful data manipulation, sort and ETL
product. Visual Syncsort adds an easy-to-use GUI. Sigma is a
specialized aggregation engine. FilePort is a two-way data conversion
utility. Backup Express is an enterprise-wide, multi-platform backup
and restore solution. Visit http://www.syncsort.com/tgz710 for FREE
TRIALS, more information and a FREE multi-tool.

=======================================
MORE GREAT STUFF ON SEARCHDATABASE!
=======================================

More high-quality SQL Server tips, tutorials, and scripts from around
the web:
http://searchdatabase.techtarget.com/bestWebLinks/0,289521,sid13_tax282894,00.html

Our collection of over 200 original DBA, developer, Oracle, DB2, and
SQL Server tips FREE to searchDatabase members!
http://searchdatabase.techtarget.com/tips/0,289484,sid13_tax281808,00.html

Listen to an audio chat with SQL guru Joe Celko:
http://searchdatabase.techtarget.com/onlineEventsTranscriptSecurity/1,289693,sid13_gci558024,00.html
The top SQL expert in the world gives a presentation on "SQL for
Smarties" about advanced SQL techniques, and answers audience
questions. He gives some great tips--check it out!

Ask the Expert category of the day: "SQL Server"
http://searchdatabase.techtarget.com/ateAnswers/0,289620,sid13_tax285648,00.html
Gurus Mike Hotek, Jorge Cano, and Bianca Blount are available to
answer your toughest SQL Server questions. 

Our Featured Topic this week: "Data mining for fun and profit"
http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci754839,00.html
Data mining tools attempt to predict future trends and behaviors,
allowing businesses to make proactive, knowledge-driven decisions. Is
data mining right for you? Read our collection of expert tips and
advice...

Fabian Pascal's latest rant against XML data management:
http://searchdatabase.techtarget.com/tip/1,289483,sid13_gci752631,00.html

Hot topic in our Discussion Forums: "SQL Server error"
http://searchdatabase.techtarget.com/forums/0,289802,sid13_fid1,00.html
Tony is getting Error 18483 when using the SQL Server 2000
Replication wizard. Can you help?

=======================================
LEARNING ZONE FEATURED BOOK OF THE WEEK 
=======================================
"Teach Yourself ADO 2.5 in 21 Days" 

By Christoph Willie 

This book's coverage includes basic implementation of ADO 2.5 (i.e.,
data manipulation, working with recordsets, using stored procedures),
advanced techniques (i.e., how to work asynchronously, data shaping,
building applications, database management), and using ADO in a
distributed environment (i.e., designing COM+ apps, the in-memory
database, disconnecting data, using remote data service, debugging
and tuning). 

http://www.digitalguru.com/dgstore/product.asp?isbn=0672318733&ac_id=58 

================================================ 
SUBMIT A TECHNICAL TIP AND WIN A PRIZE! 
================================================ 
Do you have a time-saving shortcut, trick, or script that you want to
share with other database pros? The first fifty individuals who
submit a tip will receive a free searchDatabase.com hat. The highest
rated tips each month will win our "Tip of the Month" contest and
receive a high-quality searchDatabase.com denim shirt AND a free book
of your choice from Wrox Press. We're accepting short, focused tips
or code snippets on topics of interest to DBA's and database
developers, such as Oracle, DB2, SQL Server, database design, SQL,
performance tuning, etc. Click here for more info and to submit your
tip: 

http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz520733_cts520732,00.html
 

This will be a great way to share your knowledge, cement your status
as an industry expert, and maybe win a prize. Send us your tip today!

================================================ 
If you would like to sponsor this or any TechTarget newsletter,
please contact Gabrielle DeRussy at [EMAIL PROTECTED] 
================================================ 



If you no longer wish to receive this newsletter simply reply to 
this message with "REMOVE" in the subject line.  Or, visit 
http://searchDatabase.techtarget.com/register 
and adjust your subscriptions accordingly. 

If you choose to unsubscribe using our automated processing, you 
must send the "REMOVE" request from the email account to which 
this newsletter was delivered.  Please allow 24 hours for your 
"REMOVE" request to be processed.

Reply via email to