Multiple Inserts to same table

Question

Multiple Inserts to same table

John Foreman-214519

SSC Rookie

Points: 30
More actions
June 6, 2007 at 10:36 am

#172653

Hi,
I have a grid of computers inserting to one table in a database. I am suffering from a huge amount of deadlocks as they all try and access the same page at the same time as there was a primary key on the table with an identity column.
I have created a different PK now based on a checksum to try and force the inserts to use different pages spread throughout the table but there are still too many lock contentions.
Does anybody know, or has anybody used, a better method of multiple inserts avoiding locking contention?
Your help will be much appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Anders Pedersen SSChampion Points: 11410 More actions · Answer 1

Try to create a clustered index that spreds the inserts around. (might have been what you did when you changed your PK, but make sure).

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Inserts by themselves should not be able to deadlock. that requires 2 or more commands in a transaction ussually. Is there a trigger on that table?

You can try setting the clustered index properties so that it doesn't allow page locks. Then your inserts will just take row locks and the contention should go away.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 3

Hi John

Heard of Row versioning in SQL 2005? Snapshot isolation does provide it. The following document would give you an idea of how to handle snapshot isolation in sql 2005. http://sqlserverfaq.com/controls/kbase/store/KB_17_Delaney_concurrency.pdf

As you mentioned about the lock escaltions, you can use the trace flag 1211,1224 It is riski to use it in applications but it avoids lock escaltions at session levels. checkout http://msdn2.microsoft.com/en-us/library/ms188396(SQL.90).aspx

I tried using trace flag 1211 and snapshot Isolation to load chunks of data into same table in 2-3 connections, It worked fine. You need to carry out some test as per your application behaviour. if you want to go for it, please remember to

1. Manage your Tempdb database efficiently http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

2.Managing Lock Memory using using the locks parameter of sp_configure.

Regards
Shrikant Kulkarni

John Foreman-214519 SSC Rookie Points: 30 More actions · Answer 4

Hi All,

many thanks for the replies. Actually when I took the primary key off the table the deadlocks disappeared, I guess this may be the same as removing page level locking. Also, I had filled out each row to consume a page but I will experiment with this to see if it helped or not.

Thanks for your prompt replies.