Add DuplicateKeyException to DAO exception hierachy [SPR-5125]

[spring-projects-issues]

Ben Rowlands opened SPR-5125 and commented

The Spring DAO exception hierarchy is not fine grained enough to isolate duplicate key errors. A duplicate key error is an example of where an application may catch the exception and invoke alternative logic instead of just letting it fall down the stack.

For example, we read messages from a queue and insert the data into a database. We explicitly catch a duplicate key insert exception as this indicates we've processed the same message earlier. This is more efficient than a guarding the update with a check-then-act (which also introduces a race condition that may lead to dup-key exception anyway).

Additionally, if we use single-phase commit for our JMS and DB transactions (commit DB first then JMS) a failure scenario is that the DB transaction commits but the JMS transaction rollsback. In this case the message is delivered again and we get a dup-key exception. We can then safely ignore such duplicate messages.

A dup-key error is currently classified as a DataIntegrityViolationException along with other errors such as a data bounds error so we can't simply catch this exception type. We could work around this be checking the vendor error-code/state however this is not portable and cumbersome.

Ideally an exception like:

public class DuplicateKeyException extends DataIntegrityViolationException { }

Spring already provides mappings from vendor specific codes so it would be very powerful if it could provide a fine grained exception to allow this common duplicate-key handling pattern to be detected just using exception type.

We currently use the following SQLExceptions to detect a dup-key:

• Sybase: error-state=23000
• DB2: error-state=23505

---

Issue Links:

• SPRNET-1215 Add DuplicateKeyException to DAO exception hierachy ("is depended on by")
• SPRNET-1216 Change exception translation mappings to throw DuplicateKeyException ("is depended on by")
• Add DuplicateKeyException to DAO exception hierachy [SPR-5126] #9799 Add DuplicateKeyException to DAO exception hierachy ("is duplicated by")
• Subclass DataIntegrityViolationException to represent main constraint types and translate SQL exceptons into appropriate subclass [SPR-2977] #7663 Subclass DataIntegrityViolationException to represent main constraint types and translate SQL exceptons into appropriate subclass

Referenced from: commits a0139ab

1 votes, 2 watchers

[spring-projects-issues]

Ben Rowlands commented

This is similar (a more specific request) than #7663

[spring-projects-issues]

Ben Rowlands commented

Actually, we also need to check the vendor error-code in Sybase (FK violations also map onto 23000):

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/refman/`@Generic__BookTextView`/27249

The error code 2601 specifies we have a duplicate key:

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/svrtsg/`@Generic__BookTextView`/34048;pt=15380