Need field uniqueness but don't want index
in [Server Configuration]
|
Prev: Page Splits
Next: RPC vs. RPC Out for Linked Servers
From: OceanDeep via SQLMonster.com on 11 Feb 2010 13:41 Jay, thank for the quick reply. I had a typo in my previous email. When I wrote "...Field C combined with B and C is to make the row unique and field C is frequently updated. .." I meant to write "......Field A combined with B and C ..". The three fields combined will make each row unique to the entire table. fields A and B or field C by itself doesn't make each row unique. Jay wrote: >Or, does column C have to be unique in the entire table, as well as A & B? > >> If A & B are the key, then by definition, they must be unique. If, >> however, C is required to make the value unique, then A, B & C would be >[quoted text clipped - 36 lines] >>>>> >>>>> OD -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1
From: OceanDeep via SQLMonster.com on 11 Feb 2010 13:49 Tom, the reason I asked the question is because the unqiue index (field A, B, C) in the table involved in a deadlock from an Update and Insert statement. Both statement only touches the one table. This deadlock only happens once in a long while (once every couple weeks and sometimes nothing for a long while) The update statement using A and B as a Key to update a few fields including field C. I was suspecting that the field C being part of the index and get updated causing some timing issue. But it is still possible that there is some extreme user entry operation from the web browser that I haven't thought of yet. Tom Cooper wrote: >It's hard to say which would be more efficient. Even if one of us ran a >test, it wouldn't necessarily apply to your situation because, as always, >YMMV. My guess is that the performance will be close either way. Given >that, I absolutely would go with the unique index. It's easy and quick to >implement. Also, unique constraints are ALWAYS enforced. If you use a >trigger, there are ways to insert rows where the trigger doesn't fire. > >So I would definitely go with the unique index. That's easy and quick to >implement. Then, if your testing indicates you have performance problems, >and that this index is the cause, only then try the trigger. I suspect, >though, that if you have performance problems, it won't be helped >significantly by using a trigger, and you will have to look elsewhere for >the performance fix. > >Tom > >> Say I have field A, B, C. A and B are the ones being used in Key join and >> where-clause and I would create an index for it. Field C combined with B >[quoted text clipped - 24 lines] >>>> >>>> OD -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1
From: Tom Cooper on 11 Feb 2010 16:56 It's possible that having this unique index is causing your deadlocks. The only way to know is to analyze the statements and the locks and deadlock chain. And possibly the best solution would be to use a trigger to enforce the uniqueness. But there are other choices, which may be better. First, I assume your table has a clustered index. But if it doesn't, you should seriously consider having one. That may improve performance and help prevent deadlocks. Second what is your transaction isolation level? If it is REPEATABLE READ or even worse SERIALIZABLE, consider whether or not the READ COMMITTED level is sufficient. If it is, that may help prevent deadlocks. Also, do what you can in your program to minimize deadlocks and minimize the impact of deadlocks when they occur. One way to minimize deadlocks is to spend as short of time as possible. You can minimize the impact by having your program detect the deadlock and when it occurs, the program knows it's updates have been rolled back, so the program can just redo the updates. That way, the user only notices a slightly longer response time rather than being given a deadlock. What the best choice is will depend on your situation. Tom "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message news:a37c51f8ed0e4(a)uwe... > Tom, the reason I asked the question is because the unqiue index (field A, > B, > C) in the table involved in a deadlock from an Update and Insert > statement. > Both statement only touches the one table. This deadlock only happens > once > in a long while (once every couple weeks and sometimes nothing for a long > while) The update statement using A and B as a Key to update a few fields > including field C. I was suspecting that the field C being part of the > index > and get updated causing some timing issue. But it is still possible that > there is some extreme user entry operation from the web browser that I > haven't thought of yet. > > Tom Cooper wrote: >>It's hard to say which would be more efficient. Even if one of us ran a >>test, it wouldn't necessarily apply to your situation because, as always, >>YMMV. My guess is that the performance will be close either way. Given >>that, I absolutely would go with the unique index. It's easy and quick to >>implement. Also, unique constraints are ALWAYS enforced. If you use a >>trigger, there are ways to insert rows where the trigger doesn't fire. >> >>So I would definitely go with the unique index. That's easy and quick to >>implement. Then, if your testing indicates you have performance problems, >>and that this index is the cause, only then try the trigger. I suspect, >>though, that if you have performance problems, it won't be helped >>significantly by using a trigger, and you will have to look elsewhere for >>the performance fix. >> >>Tom >> >>> Say I have field A, B, C. A and B are the ones being used in Key join >>> and >>> where-clause and I would create an index for it. Field C combined with >>> B >>[quoted text clipped - 24 lines] >>>>> >>>>> OD > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1 >
From: Tom Cooper on 11 Feb 2010 17:02 "Tom Cooper" <tomcooper(a)comcast.net> wrote in message news:%23E9TdU2qKHA.1800(a)TK2MSFTNGP02.phx.gbl... <snip> > Also, do what you can in your program to minimize deadlocks and minimize > the impact of deadlocks when they occur. One way to minimize deadlocks is > to spend as short of time as possible. <snip> That should have been "One way to minimize deadlocks is to spend as short of time ==in transaction state== as possible. Tom
From: OceanDeep via SQLMonster.com on 11 Feb 2010 17:42
Yes, we do have a cluster index which is just an identify field. We use the default Isolation level, Read Committed. I will take a look at the sp that contains the insert and update again. These two statements are actually very simple and short by itself. We are using auto commit (implicit tran) for the two statements. Tom Cooper wrote: >It's possible that having this unique index is causing your deadlocks. The >only way to know is to analyze the statements and the locks and deadlock >chain. And possibly the best solution would be to use a trigger to enforce >the uniqueness. But there are other choices, which may be better. > >First, I assume your table has a clustered index. But if it doesn't, you >should seriously consider having one. That may improve performance and help >prevent deadlocks. > >Second what is your transaction isolation level? If it is REPEATABLE READ >or even worse SERIALIZABLE, consider whether or not the READ COMMITTED level >is sufficient. If it is, that may help prevent deadlocks. > >Also, do what you can in your program to minimize deadlocks and minimize the >impact of deadlocks when they occur. One way to minimize deadlocks is to >spend as short of time as possible. You can minimize the impact by having >your program detect the deadlock and when it occurs, the program knows it's >updates have been rolled back, so the program can just redo the updates. >That way, the user only notices a slightly longer response time rather than >being given a deadlock. > >What the best choice is will depend on your situation. > >Tom > >> Tom, the reason I asked the question is because the unqiue index (field A, >> B, >[quoted text clipped - 33 lines] >>>>>> >>>>>> OD -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201002/1 |