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 |
|
almalexia
Starting Member
1 Post |
 Posted - 2008-06-09 : 05:38:27
|
| Hi I have a few things to clear up and I hope i can find some answers in here.I have an application written in C# using COM+ componentsThis application is intended to support a few hundred users at the same time and each action user leads to a few updates on several tables.The problem is that 2 or more users might do this thing the same time meaning that each users should update rows that "belong" the other users .I rely on the appearance of deadlocks in order to keep the data consistent, so only one of the users should be able to have this action completed, the transactions for the other concurrent users should abort. Basically the results would be the same no matter which user will complete the transaction so the only issue is to have only one action completed. So far so good, it seems that I have no problems and that the data is consistent.Now the reason why I'm here would be that lately i see that the number of deadlocks has increased considerably and there should be no reason for this to happen; the situations when the users would modify each others data are somehow rare and I should see a few hundred deadlocks dailySo what i think is that SQL might increases the lock granularity to table or page instead of using row locks(I am not sure it's just a wild guess)From what I've read, sql starts with default row lock and it might increase it when necessary.And now finally the question: can I force sql to use only row lock? and if yes are there any risks involved? Is sql capable to maintain only this kind of lock and still be able to manage the transactions correctly? And another question, how do deadlock decrease overall performance? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-06-09 : 22:27:51
|
| Default is page lock in sql server, but you can use row lock hint in your queries. |
 |
|
|
|
|
|
lock granularity and performance issues