Need field uniqueness but don't want index
in [Server Configuration]
|
Prev: Page Splits
Next: RPC vs. RPC Out for Linked Servers
From: Jay on 11 Feb 2010 18:27 I think you should post the table DDL as it relates to the A, B & C fields as well as any constraints and indexes that reference the key fields. I find it a tad strange that you would be frequently updating the 3rd component of a PK and I find you reference to "identify" too close to "IDENTITY(1,1)" for comfort. If, in fact, you have an IDENTITY column in the key, then it's unique by itself. "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message news:a37e5aeef1e5a(a)uwe... > 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 >
From: Kalen Delaney on 11 Feb 2010 18:36 Actually, IDENTITY columns are not guaranteed to be unique... unless you have a unique index on them. -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "Jay" <spam(a)nospam.org> wrote in message news:Ol9kIH3qKHA.5940(a)TK2MSFTNGP02.phx.gbl... > I think you should post the table DDL as it relates to the A, B & C fields > as well as any constraints and indexes that reference the key fields. > > I find it a tad strange that you would be frequently updating the 3rd > component of a PK and I find you reference to "identify" too close to > "IDENTITY(1,1)" for comfort. > > If, in fact, you have an IDENTITY column in the key, then it's unique by > itself. > > "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message > news:a37e5aeef1e5a(a)uwe... >> 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 >> > >
From: Jay on 11 Feb 2010 21:16 I know that's true, sorry about that. Still, unmolested, an identity column will be unique - and most aren't futzed with. "Kalen Delaney" <please_reply_on_newsgroups(a)domain.com> wrote in message news:eTggTM3qKHA.3536(a)TK2MSFTNGP06.phx.gbl... > Actually, IDENTITY columns are not guaranteed to be unique... unless you > have a unique index on them. > > -- > HTH > Kalen > ---------------------------------------- > Kalen Delaney > SQL Server MVP > www.SQLServerInternals.com > > "Jay" <spam(a)nospam.org> wrote in message > news:Ol9kIH3qKHA.5940(a)TK2MSFTNGP02.phx.gbl... >> I think you should post the table DDL as it relates to the A, B & C >> fields as well as any constraints and indexes that reference the key >> fields. >> >> I find it a tad strange that you would be frequently updating the 3rd >> component of a PK and I find you reference to "identify" too close to >> "IDENTITY(1,1)" for comfort. >> >> If, in fact, you have an IDENTITY column in the key, then it's unique by >> itself. >> >> "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message >> news:a37e5aeef1e5a(a)uwe... >>> 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 >>> >> >>
From: Kalen Delaney on 11 Feb 2010 21:20 But if you need to have it guaranteed unique ... you just can't trust that it has never been 'futzed with'.... -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "Jay" <spam(a)nospam.org> wrote in message news:#11Yfl4qKHA.4752(a)TK2MSFTNGP04.phx.gbl... > I know that's true, sorry about that. Still, unmolested, an identity > column will be unique - and most aren't futzed with. > > "Kalen Delaney" <please_reply_on_newsgroups(a)domain.com> wrote in message > news:eTggTM3qKHA.3536(a)TK2MSFTNGP06.phx.gbl... >> Actually, IDENTITY columns are not guaranteed to be unique... unless you >> have a unique index on them. >> >> -- >> HTH >> Kalen >> ---------------------------------------- >> Kalen Delaney >> SQL Server MVP >> www.SQLServerInternals.com >> >> "Jay" <spam(a)nospam.org> wrote in message >> news:Ol9kIH3qKHA.5940(a)TK2MSFTNGP02.phx.gbl... >>> I think you should post the table DDL as it relates to the A, B & C >>> fields as well as any constraints and indexes that reference the key >>> fields. >>> >>> I find it a tad strange that you would be frequently updating the 3rd >>> component of a PK and I find you reference to "identify" too close to >>> "IDENTITY(1,1)" for comfort. >>> >>> If, in fact, you have an IDENTITY column in the key, then it's unique by >>> itself. >>> >>> "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message >>> news:a37e5aeef1e5a(a)uwe... >>>> 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 >>>> >>> >>> > >
From: Jay on 11 Feb 2010 21:41
True, but I've been trying to pry information out of the OP. So, I've been trying a shorter route. "Kalen Delaney" <please_reply_on_newsgroups(a)domain.com> wrote in message news:OGcVun4qKHA.4604(a)TK2MSFTNGP05.phx.gbl... > But if you need to have it guaranteed unique ... you just can't trust that > it has never been 'futzed with'.... > > -- > HTH > Kalen > ---------------------------------------- > Kalen Delaney > SQL Server MVP > www.SQLServerInternals.com > > "Jay" <spam(a)nospam.org> wrote in message > news:#11Yfl4qKHA.4752(a)TK2MSFTNGP04.phx.gbl... >> I know that's true, sorry about that. Still, unmolested, an identity >> column will be unique - and most aren't futzed with. >> >> "Kalen Delaney" <please_reply_on_newsgroups(a)domain.com> wrote in message >> news:eTggTM3qKHA.3536(a)TK2MSFTNGP06.phx.gbl... >>> Actually, IDENTITY columns are not guaranteed to be unique... unless you >>> have a unique index on them. >>> >>> -- >>> HTH >>> Kalen >>> ---------------------------------------- >>> Kalen Delaney >>> SQL Server MVP >>> www.SQLServerInternals.com >>> >>> "Jay" <spam(a)nospam.org> wrote in message >>> news:Ol9kIH3qKHA.5940(a)TK2MSFTNGP02.phx.gbl... >>>> I think you should post the table DDL as it relates to the A, B & C >>>> fields as well as any constraints and indexes that reference the key >>>> fields. >>>> >>>> I find it a tad strange that you would be frequently updating the 3rd >>>> component of a PK and I find you reference to "identify" too close to >>>> "IDENTITY(1,1)" for comfort. >>>> >>>> If, in fact, you have an IDENTITY column in the key, then it's unique >>>> by itself. >>>> >>>> "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message >>>> news:a37e5aeef1e5a(a)uwe... >>>>> 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 >>>>> >>>> >>>> >> >> |