Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2009-08-27 : 10:43:10
|
| We have a database with 18 tables, 1 Header and 17 children. The PRIMARY KEY (nonclustered) is an auto-incremented IDENTITY value (TransactionID). We have a load process that inserts into the Header table, retrieves the TransactionID, and inserts into the children (through stored procedures).The load is multi-threaded, and each thread will process 100 transactions per batch (wrapped in a transaction context). So we could have 4 threads, each with a batch of 100 transactions. However, the more threads the more deadlocks we are experiencing. We ran a profiler, and it appears that the processes involved in teh deadlock are requesting RANGE S-U Key locks on the primary keys. I have no idea what to do about this, or how to resolve.Are there any hints I can specify to prevent sql holding range locks? There will never be any overlap because the PK is an auto-generated key. We just want to dump the data into the database as quickly as possible, but also retain the integrity/fk relationships. Any help will be greatly appreciated!Hearty head pats |
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2009-08-27 : 10:52:29
|
| How are you retrieving the TransactionID? Are you not using scope_identity()?Also your Primary key should be clustered if you're using an identity value.An infinite universe is the ultimate cartesian product. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-08-27 : 10:53:19
|
| see if this helpshttp://articles.techrepublic.com.com/5100-10878_11-5181472.html |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2009-08-27 : 11:37:04
|
| Hi thereThankyou both for your replies.In answer to your questions, we are using Scope_identity, and the reason for making the PK non-clustered is to prevent contention. If 100's of trans per sec are trying to access a sequential pk, then this can cause blocking issues as they will all be trying to access the same location and same page.I shall havea look at the article, and see if this helps.In the meantime, I have changed the isolation level to read-uncommitted, as we are only doing inserts (no reads, updates, etc)I know this may sound like a silly question, but how does sql server handle IDENTITY assignment. Is there a scenario where sql server can assign the same identity value? for example, if multiple transactions are retrieving IDs and none have yet been committed?Hearty head pats |
|
|
|
|
|
|
|
|
Deadlocking all over the place