================================================ 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.