Concurrency and deadlocks...what should i do

Question

Hello forums,

Ive been reading a  bit on concurrency and deadlocks while i doubt i understand it 100% i thhink i got the general basics. But according to my reading material (

Microsoft® SQL Server® 2008 T-SQL Fundamentals (PRO-Developer) [Paperback])

The Default isolation leel is READCOMMITED and i can set the lock time out to 5 seconds so if a deadlock occurs one transaction is killed...etc etc

but the thing is thats all i can see that needs to be done but according to my reading, dead locks and concurrency problems are one of the worst thing that could happen to a database. But i dont know what else to do cause according to my untrained eye everything has already been accounted for. So I was just looking for any general tips or stuff to look out for. Thanks

Answer 1

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/01/reproducing-deadlocks-involving-only-one-table.aspx

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/26/reproducing-one-more-intermittent-deadlock-on-only-one-table.aspx

 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Consider OPTIMISTIC concurrency control using rowversion (timestamp formerly) column:

http://www.sqlusa.com/articles2005/rowversion/

Deadlock prevention: http://www.sqlusa.com/bestpractices/deadlock/

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 3

why wouldnt just setting lock limit to 5 seconds solve everything?

Answer 4

why wouldnt just setting lock limit to 5 seconds solve everything?

Would your company's president be overly happy if you drop a $100,000 transaction?

Instead of mechanical solution, you have to focus on the business scenario. You have to make sure that your solution makes business sense.

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 5

What do you mean by "what should I do"?

If you are asking about what to take in mind to avoid deadlocks, then:

- Use short transactions

- Access resources in the same order

- Use the lower isolation level needed for your task

- Avoid user interaction in transactions

- Use a row versioning-based isolation level

Minimizing Deadlocks
http://msdn.microsoft.com/en-us/library/ms191242.aspx

Here is a serie of blog posts, by Bart Duncan, about deadlock troubleshooting.

http://blogs.msdn.com/b/bartd/archive/tags/sql+deadlocks/

Here is a serie of blog posts, by Sunil Agarwal, about concurrency.

http://blogs.msdn.com/b/sqlserverstorageengine/archive/tags/concurrency/

---

AMB

Some guidelines for posting questions...

Answer 6

ok well ive been reading this book and there was an issue where the select couldnt run because of an exclusive lock on the same table. The companies president would not be mad if he was doing a select and it failed because of an update which takes ... what...a second?

he can just re-run his query after and get thru...

right?

Answer 7

Correct.

The company's president would be sligthly annoyed if a query takes too long.

But the same company's president would be real mad if you drop a $100,000 transaction due to concurrency problems.

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 8

how will the transaction drop,,, all he has to do is try again when the first transaction releases the lock or hopefully it doent get deadlocked

Also the best isolation level is read commited which is already the default in SQL

Answer 9

I meant if an employee from accounting was entering a $100,000 transaction and the system dropped it due to insufficient concurrency design.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 10

ok,  when you say insufficient cncurrency design what do you mean, is it the cases where two tracsactions have a lock on a table but they need to select the record from each other to continue?

Answer 11

If 10 users from accounting entering financial transactions (like $100,000 payment), rain or shine, you have to make sure that none of the transactions lost due to inferior concurrency control design .... or you will get a call from the CFO-s secretary for a very unpleasant meeting with the CFO ....

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 12

ok but doesnt an exclusive lock already take care of that?

Answer 13

Looking at the alexander_kuznetsov examples, they all show deadlocks example but not how to over come them.

But also it seems to me that locks and probably some deadlocks  cannot be avoided

Answer 14

But also it seems to me that locks and probably some deadlocks  cannot be avoided

Probably so. For fast, smooth operating OLTP system, the tables must be designed narrow and fixed column width, the T-SQL transactions must be quick and efficient.

Read at my article on OPTIMISTIC concurrency. Basically you "peek" before UPDATE to see if anything changed (rowversion column) since reading that record.  Alternative is PESSIMISTIC concurrency control.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 15

ok i shall study this an repost in a few weeks , tanks