How do I avoid getting duplicate key errors when using findOrSaveBy dynamic finders in Grails?

Question

In a concurrent situation (asynchronous file uploads) where I try to create (or reuse) and attach keyword tags to images in an image library application i get a Unique index or primary key violation when creating Tag domain objects.

The controller calls a service method to add an image to the library and also the keyword tags that belongs to it.

Am I going about this the wrong way or am I missing something? I'm currently using Grails 2.3.5.

Code:

class ImageAssetService {

    def addTagToImage(ImageAsset imageAsset, String kw, String locale) {
        def tagName = kw.toLowerCase(Locale.forLanguageTag(locale))
        // OFFENDING LINE NEXT
        def tag = Tag.findOrSaveByNameAndLocale(tagName, locale, [lock: true])
        imageAsset.addToTags(tag)
        if(!imageAsset.save()) {
            throw new ImageAssetException()
        }
        imageAsset
    }

    def addTagsToImage(ImageAsset imageAsset, Set<String> keywords, String locale) {
        keywords.each { kw ->
            addTagToImage(imageAsset, kw, locale)
        }
        imageAsset
    }

    // CONTROLLER CALLS THIS METHOD
    def addImageAsset(InputStream inputStream, String filename, long fileSize, long authorId, String timeZoneOriginal, String heading, String description, Set tags, String imageCollectionName) {

        // Create the ImageAsset domain object
        ImageAsset imageAsset = new ImageAsset(
            filename: filename,
            fileSize: fileSize,
            author: Author.get(authorId),
            timeZoneOriginal: TimeZone.getTimeZone(timeZoneOriginal),
            heading: heading,
            description: description
        ).save()

        // Add any tags
        addTagsToImage(imageAsset, tags, 'en')

        /*
            ...
            CODE REMOVED FOR BREVITY
            ....
        */

        return imageAsset
    }

}
class Tag {

    String locale
    String name

    static hasMany = [ translations : Tag, synonyms : Tag ]

    void setName(String name) { this.@name = name.toLowerCase() }

    static constraints = {
        locale unique: ['name']
    }
    static belongsTo = ImageAsset
    static searchable = {
        except = ['locale']
        name boost: 2.0
        translations component: [prefix: 'translations_', maxDepth: 2]
        synonyms component: [prefix: 'synonyms_', maxDepth: 2]
    }
    static mapping = {
        table 'tags'
    }
}

class ImageAsset {

    String filename
    String heading
    String description
    String place
    String city
    String country
    String gps
    long fileSize = 0
    int pixelWidth = 0
    int pixelHeight = 0
    Date dateTimeOriginal
    TimeZone timeZoneOriginal

    boolean enabled = true
    Date dateCreated

    static belongsTo = [ 
        Author,
        ConceptualImageCategory,
        RepresentativeImageCategory,
        ImageCollection
    ]
    static hasOne = [ author : Author ]
    static hasMany = [
        conceptualCategories : ConceptualImageCategory,
        representativeCategories : RepresentativeImageCategory,
        collections : ImageCollection,
        metadata : Metadata,
        tags : Tag
    ]

    static constraints = {
        filename blank: false
        heading nullable: true
        description nullable: true
        place nullable: true
        city nullable: true
        country nullable: true
        gps nullable: true
        pixelWidth nullable: true
        pixelHeight nullable: true
        dateTimeOriginal nullable: true
        timeZoneOriginal nullable: true
    }

    static mapping = {
        description type: 'text'
    }

    static searchable = {
        //only = ['filename', 'heading', 'description', 'tags', 'metadata']
        author component: [prefix: 'author_']
        tags component: [prefix: 'tags_']
        metadata component: [prefix: 'metadata_']
    }
}

Error message:

Unique index or primary key violation: "CONSTRAINT_INDEX_27 ON PUBLIC.TAGS(NAME, LOCALE) VALUES ( /* key:11 */ 895, 0, 'en', 'work')"; SQL statement:
insert into tags (id, version, locale, name) values (null, ?, ?, ?) [23505-173]. Stacktrace follows:
Message: Unique index or primary key violation: "CONSTRAINT_INDEX_27 ON PUBLIC.TAGS(NAME, LOCALE) VALUES ( /* key:11 */ 895, 0, 'en', 'work')"; SQL statement:
insert into tags (id, version, locale, name) values (null, ?, ?, ?) [23505-173]
    Line | Method
->>  331 | getJdbcSQLException      in org.h2.message.DbException
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|    171 | get                      in     ''
|    148 | get . . . . . . . . . .  in     ''
|    101 | getDuplicateKeyException in org.h2.index.BaseIndex
|     68 | add . . . . . . . . . .  in org.h2.index.TreeIndex
|     52 | add                      in org.h2.index.MultiVersionIndex
|    125 | addRow . . . . . . . . . in org.h2.table.RegularTable
|    127 | insertRows               in org.h2.command.dml.Insert
|     86 | update . . . . . . . . . in     ''
|     79 | update                   in org.h2.command.CommandContainer
|    235 | executeUpdate . . . . .  in org.h2.command.Command
|    154 | executeUpdateInternal    in org.h2.jdbc.JdbcPreparedStatement
|    140 | executeUpdate . . . . .  in     ''
|    102 | doCall                   in org.grails.datastore.gorm.GormStaticApi$_methodMissing_closure2
|    105 | addTagToImage . . . . .  in se.originalab.imagedb.ImageAssetService
|     94 | doCall                   in se.originalab.imagedb.ImageAssetService$_addTagsToImage_closure3
|     93 | addTagsToImage . . . . . in se.originalab.imagedb.ImageAssetService
|     45 | addImageAsset            in     ''
|     31 | upload . . . . . . . . . in se.originalab.imagedb.UploadController
|    195 | doFilter                 in grails.plugin.cache.web.filter.PageFragmentCachingFilter
|     63 | doFilter . . . . . . . . in grails.plugin.cache.web.filter.AbstractFilter
|     53 | doFilter                 in grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter
|     49 | doFilter . . . . . . . . in grails.plugin.springsecurity.web.authentication.RequestHolderAuthenticationFilter
|     82 | doFilter                 in grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter
|   1110 | runWorker . . . . . . .  in java.util.concurrent.ThreadPoolExecutor
|    603 | run                      in java.util.concurrent.ThreadPoolExecutor$Worker
^    722 | run . . . . . . . . . .  in java.lang.Thread

Answer 1

I found a solution myself. I had to break out the creation of a Tag into it's own transaction. Each call to Tag.findOrSaveByNameLocale() is now called through a synchronized service method from the controller. Then I add them to the ImageAsset afterwards.

One problem with this is that if the creation of the ImageAsset fails the Tags will still be persisted, but in my use case this is not a big problem.