Table Hints and Deadlocks Advice

Question

Hi,

Can someone please offer any advice on the following INSERT statement (which I have
simplified for illustrative purposes):

INSERT

     tbl_A WITH (ROWLOCK,XLOCK)

      (

      ColA 

)    

SELECT

      ColA

FROM

      SourceA s

WHERE

      NOT EXISTS (SELECT 1 FROM tbl_Al WITH (ROWLOCK,XLOCK) WHERE s.ColA = l.ColA)

There are several processes which insert into this table based on different sources.

Before I added my table hints (which I have taken a educated guess at, after reading BOL) I was
getting either a Deadlock Error in one of the processes or duplicate ColA’s in
tbl_A.  Any advance much appreciated.

---

BoroFC

Answer 1

It's difficult to say what is the best solution without knowing the full story.

The protection againt PK errors would be:

BEGIN TRANSACTION

INSERT tbl (...)
   SELECT ...
   FROM   source
   WHERE  NOT EXISTS (SELECT * FROM tbl WITH (SERIALIZABLE) WHERE ...)

COMMMIT TRANSACTION

But the serializable isolation level is prone to deadlocks.

It sounds a bit funny when you say that there are multiple processes inserting rows from multiple sources. Is it really a matter of that the process that comes first wins, or is there an UPDATE further on?

I'm asking this, because you may have to consider the entire process, not only the INSERT statement.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Thanks for your response.  Yes it's 1st come 1st served.  I can understand the need for SERIALIZABLE in the EXISTS, although it does seem like overkill to me, won't this lock the entire table?  But surely there is no need for the explicit transaction around a single insert statement?   Basically I'm using the tableA as a means to get a Unique ID back to the source Table, so the second statement is:

UPDATE
	s 
SET
	s.UniqueID = l.UniqueID
FROM
	SourceA s
JOIN
	table_A l WITH (NOLOCK) ON l.ColA= s.ColA 

---

BoroFC

Answer 3

Do many concurrent connections perform this statement ?So read Tony's article

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx

---

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

Answer 4

Yes there are 3 at the moment and this will increase to potentually 24!

The article is dealing with a single row insert.  I wish to to do a multi row insert?

---

BoroFC

Answer 5

It does not matter the Principe is the same

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

Answer 6

Consider optimistic concurrency control:

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

OCC is based a rowversion data type column which is maintained by SQL Server.

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 7

SERIALIZABLE will not lock the entire table, but it will take out range locks, so the effect might be about the same. I don't see a wonder of concurrency before my eyes.

If I understand this right, you have a process that comes and have the values A, B, C and D and wants unique keys for these. Therefore it inserts rows from this table. The current id is 9876. A and D are already in the table, and the final outcome is something like:

A = 4651
B = 9877
C = 9878
D = 7812

Which version of SQL Server are you using?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

SQLUSA, not sure you approach is what I want, as this is a Batch Process.

Erland  Sommarskog -

So lets just say tbl_A consists of:

A = 4651
B = 9877
C = 9878
D = 7812

And Source table SourceA consists of:

A

E

Then E should get inserted with a new unique ID.  But the problem could be that the process for SourceB may be running concurrently and it consists of:

A

E

So only one value of E should exist in the destination table tbl_A, without causing a deadlock.

I've gone with this approach "http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/06/30/855.aspx" for now and will monitor over the next few days.

Col.

---

BoroFC

Answer 9

Let's start over. Can you describe the issue in detail?  Thanks.

Deadlock prevention article:

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

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 10

Let's start over. Can you describe the issue in detail?  Thanks.

Kalman, I think Col has given a description on sufficient level for the time being. As he says he will go with what it's outlined in Tony Rogerson's post, we can put it aside for now.

So much is clear, this is not the place for optimistic concurrency. That relates to simultaneous updates - this is about simultanoues inserts and id generation.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 11

Erland,

I am not in favor of locking a table or part of it. That approach frequently produces results which are not dissimilar to walking on an icy slope.

Using INT IDENTITY SURROGATE PRIMARY KEY provides superfast INSERT from multiple connections in most cases. If large insert, batching is the solution or running it off hours.

Adding to the fast INSERT issue, well maintained indexes are necessary with FILLFACTOR on dynamic tables. It is also helpful if columns are fixed size and the table as narrow and possible. Transactions must be lean & mean.

If the INSERTs are coming from uncontrolled sources, INSERT trigger may be necessary to sherif the hectic INSERT activity.

Finally, the entire database system must be optimized:

http://www.sqlusa.com/articles/query-optimization/

Same for disk resources:

http://www.sqlusa.com/bestpractices/configureharddisk/

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 12

Thanks Guys much appreciated.  I'll leave the solution I have in place to run for a day (as I'm out of the office tomorrow). 

SQL USA - I'll digest some of your articles.  I'm not too bothered if the Tony Regerson approach isn't the best performance wise, as long as it stops the Duplicates and Deadlocks.

---

BoroFC

Answer 13

SQL USA - I'll digest some of your articles.  I'm not too bothered if the Tony Regerson approach isn't the best performance wise, as long as it stops the Duplicates and Deadlocks.

The little I know about your system, I would venture to say that Tony's article may not be applicable for your case. Whenever I hear locking / locks / deadlocks, I think of good database design, good indexing and good programming. To prevent duplicates, you need UNIQUE index or UNIQUE KEY (which is based on unique index).

Granted many times a database developer is not in a position to change the database design. Indexing and programming easier to change. If you post sufficient information (table design, index design, code), we will be able to provide you with better assistance.

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 14

It would appear that Tony Rogerson solution is not right for me. 

I still get both deadlocks and Primary Key failures, all be it at a much less frequency.  My next approach is to use a SERIALIZABLE lock as suggested by Erland, error trap a deadlock and re-run the code. 

SQLUSA I get what you are saying and I may implement a batch approach to my transactions and keep them "lean and mean".  The only issue I have with this approach, is surely you are just reducing the frequency of an deadlocks or Primary Key failures?  Thanks for keeping an eye on this thread, I've just been out of the office for 5 days.

---

BoroFC

Answer 15

SQLUSA I get what you are saying and I may implement a batch approach to my transactions and keep them "lean and mean".  The only issue I have with this approach, is surely you are just reducing the frequency of an deadlocks or Primary Key failures?  Thanks for keeping an eye on this thread, I've just been out of the office for 5 days.

---

We need to establish first if the two issues are related.

Deadlocks, with high likelyhood, can be resolved mostly with coding & index changes or regular index & statistics maintenance. See my posted deadlock link above.

Your system must be fully optimized not only for deadlock reduction but blocking reduction as well. See my posted optimization link above. Ask yourself, why run a database system which is not fully optimized? The only "legitimate" answer: I inherited a bad design & management did not give the green light (and resources) to change it. But even with a poorly designed database you can do miracles if you follow the optimization principles in my article.

I don't think mechanical approach will lead you to success (Tony's or Erland's methods - both of them I respect highly). You need to concentrate on the business scenario. Business wise it has to make sense what you are doing.

Can you post the table and index design related to the PRIMARY KEY in question? INSERT code as well. We need the "real thing" if you want quick assistance. Thanks.

---

Kalman Toth SQL SERVER & BI TRAINING