Sam Mason <sam@samason.me.uk> wrote:
> Still doesn't really describe the
> engineering rational behind it though.
Well, the distinctions in many cases would be mostly of interest to a
DBA managing a large shop who was trying to characterize the reasons
for query failure. Some codes, however, are particularly valuable.
At the low end, classes '00' (information), '01' (warning), and '02'
(no rows affected) can be used for useful, if mundane, purposes. A
really interesting one is '40001' -- which indicates that your
transaction was rolled back because of conflicts with concurrent
transactions. Our framework, for example, resubmits transactions
which fail with this SQL state; the user, and indeed the application
code, never have any indication that the transaction was rolled back
and restarted -- it appears just the same as a delay caused by
blocking. (Our logs, of course, track these, so we can look to reduce
conflicts.)
> I still stand by my assertion that the constraint name is sufficient
> for the original purpose.
After thinking about that some more, I think I'm sold.
-Kevin