Index duplicates and transactions
Erland Sommarskog
BEGIN TRANSACTION
UPDATE...
INSERT INTO table (col1, col2,col3)
SELECT col1, col2, col3
FROM some_other_table
DELETE ...
UPDATE ....
COMMIT TRANSACTION
The reason I define a transaction is of course that I want all or
none of the updates done. Assume that there is some error somewhere,
so that when the INSERT statement is performed a duplicate of a
unique index is detected. Now, what would you expect the server
to do?
I might be very naive, but I think the server should rollback the
transaction, and ignore the rest of it, because the transaction was
not able to be carried out as the INSERT statement failed.
But what happens? *The stupid server continues as nothing has
happened and gladly goes on to create corrupt data!*
This is too obivious to be bug, but can anyone tell me when it is
a feature? Ans is there some way that I can tell the server to
behave properly. Yes I can add a
IF @@error != 0
BEGIN
ROLLBACK TRANSACTION
RETURN 1
END
after each update, but that's tedious and error-prone.
After all, Rdb/VMS is not a bad database. At least DEC can spell to
data consistency. At Sybase someone might vaguely have heard the
expression, but is not really sure what it means.
--
Erland Sommarskog - ENEA Data, Stockholm - som...@enea.se
Galgar {r inget man ska h{nga upp sej p}.
Graeme Sargent
>The reason I define a transaction is of course that I want all or
>none of the updates done. Assume that there is some error somewhere,
>so that when the INSERT statement is performed a duplicate of a
>unique index is detected. Now, what would you expect the server
>to do?
>I might be very naive, but I think the server should rollback the
>transaction, and ignore the rest of it, because the transaction was
>not able to be carried out as the INSERT statement failed.
>But what happens? *The stupid server continues as nothing has
>happened and gladly goes on to create corrupt data!*
>This is too obivious to be bug, but can anyone tell me when it is
>a feature? Ans is there some way that I can tell the server to
I would have thought it was obvious! It's a feature when you want to do
something else instead of or as well as rolling back. Eg log the error
somewhere or insert the row into a holding table so that
someone/something can establish whether the problem row is the new data
or the row that already existed and shouldn't have done.
>behave properly. Yes I can add a
If it ain't broke, don't fix it!
>After all, Rdb/VMS is not a bad database. At least DEC can spell to
>data consistency. At Sybase someone might vaguely have heard the
>expression, but is not really sure what it means.
You're surely not telling us that Rdb/VMS doesn't allow you NOT to
rollback?
graeme
--
Disclaimer: The author's opinions are his own, and not necessarily
those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
-m------- Graeme Sargent Voice: +44 (0)252 373035
---mmm----- Senior Database Consultant Fax : +44 (0)252 373135
-----mmmmm--- Pyramid Technology Ltd. Telex: Tell who???
-------mmmmmmm- Farnborough, Hants GU14 7PL Email: gra...@pyra.co.uk
---------------------------------------------------------------------------
We have the technology. The tricky bit is learning how to use it.
Barbara Eckman
I ran into this too, and considered it a problem. Dave Peterson of Sybase
and I logged it as a Feature Request.
Feature Request # 30636: Inserting a value which violates a unique
index returns Error 2601, Level 14, State 3. No rollback
is issued, and the transaction can go on to commit as above.
The feature request is to add the clauses "with error_abort" or
"with error_ignore" to the BEGIN TRAN or SAVE TRAN statement,
so that the programmer can specify that the transaction
should roll back if Error 2601 is encountered.
As pointed out by a prior response, there may well be situations
where you would want to ignore the fact that inserting a duplicate
key was attempted. BUT...
isn't that the reason for the WITH IGNORE_DUP_KEY option of
CREATE INDEX? In fact, the documentation of IGNORE_DUP_KEY
in the Commands Reference under CREATE INDEX (2-47) stresses
that, when IGNORE_DUP_KEY is in effect, the transaction is
permitted to continue--as if in the absence of IGNORE_DUP_KEY,
the transaction is rolled back. So, in light of the
WITH IGNORE_DUP_KEY option, the handling of Error 2601 seems
problematic, even if it was deliberate.
BTW, the workaround for this is NOT to check @@error, since this can be reset
by a trigger before you get a chance to check it. Check @@rowcount
instead.
Barbara
-------------------------------------------------------------------------
Barbara Eckman
Database Administrator
Computational Biology and Informatics Laboratory
University of Pennsylvania
eck...@cbil.humgen.upenn.edu
(215) 573-3116 Fax (215) 573-3111
-------------------------------------------------------------------------
Erland Sommarskog
>>This is too obivious to be bug, but can anyone tell me when it is
>>a feature? Ans is there some way that I can tell the server to
>
>I would have thought it was obvious! It's a feature when you want to do
>something else instead of or as well as rolling back. Eg log the error
>somewhere or insert the row into a holding table so that
>someone/something can establish whether the problem row is the new data
>or the row that already existed and shouldn't have done.
So why don't you get this behaviour when you violate a rule? And if
this bevahiour is really desired, why is such case is the entire INSERT
statement aborted, even if only one of 100 inserted rows causes a
duplicate? And is this "feature" so important that it should be the
default behaviour, or even worse the only behaviour?
Yes, I can see that someone want to do what you suggest, but I can
think of many situations where you don't care to include code to
track down the error, but you want to be dead sure that all or
nothing gets done. After all keeping the data consistent is very
important in a DBMS, and the casual user should be supported to
not slip on these details by crude defaults.
>>behave properly. Yes I can add a
>
>If it ain't broke, don't fix it!
Yes, if. But it is broke. Severly.
>>After all, Rdb/VMS is not a bad database. At least DEC can spell to
>>data consistency. At Sybase someone might vaguely have heard the
>>expression, but is not really sure what it means.
>
>You're surely not telling us that Rdb/VMS doesn't allow you NOT to
>rollback?
I don't remember the details offhand, but since Rdb pays a lot of
attention to ensure data consistency, I wouldn't expect it to
let you get away with half-done transactions. Remember that Rdb
is the DBMS which does not have any HOLDLOCK on the SELECT state-
ment, because you always get a HOLDLOCK, and there is no way you
can escape from it.
Erland Sommarskog
>BTW, the workaround for this <index duplicates> is NOT to check @@error,
>since this can be reset by a trigger before you get a chance to check it.
>Check @@rowcount instead.
Is there any value of @@rowcount that does with certainty indicate
an error? If I say:
INSERT table (col1, col2....)
SELECT f.this, g.that ...
FROM f, g
WHERE ....
I might be inserting anything from 0 to a zillion rows, if @@rowcount
is zero, that may be perfectly valid.
And by the way, cannot @@rowcount be affected by triggers too?
Well, this even more displays the need for a simple construct like
"BEGIN TRANSACTION ABORT ON ERROR", like in the feature request
Barbara wrote about.
Barbara Eckman
|>
|> Is there any value of @@rowcount that does with certainty indicate
True; in general there isn't. In a given application, however, it seems likely
that you'd know--for example, in a data entry application, by the time I get
to the point of issuing an insert statement, I expect that something will be
inserted, and that if @@rowcount = 0, something went wrong.
|> And by the way, cannot @@rowcount be affected by triggers too?
Apparently the value of @@rowcount within the trigger is not passed up
to the "calling" procedure. I say this not because I've seen it documented,
but because my applications work when I make this assumption. But it
isn't an optimal solution.
|> Well, this even more displays the need for a simple construct like
|> "BEGIN TRANSACTION ABORT ON ERROR"
Yes, I think so too.