How can you test transactional database code?

Imagine you have some kind of simple code where you insert two rows into a database within one transaction.

How can you make the second one fail in a test in order to check that the first one gets rolled back?

Would it be a webservice, I would mock it through meta-programming in order to let the second call fail.

But with database code I am unsure. Is there maybe some kind of jdbc driver which throws exceptions for (let's say) every 7th operation?

ok, let's be more specific:

Imagine you have some legacy code. It is not well structured but you also don't want to refactore it yet (let's first write some tests!). There seems to be a problem with the transactional behaviour and you would like test your code.

Since the code basically works, it would be hard to make parts of it fail by violation constraints.

So what I would like to do in my tests is to simulate what happens if the database fails (maybe because there is a deadlock, unavailability, disk full or something like that) or when you "pull the plug".

Solution

Integration test would be an appropriate place to test transactions and rollbacks. Something like this sample should suffice:

package com.example

import grails.test.mixin.integration.Integration
import grails.transaction.*
import spock.lang.*
import org.springframework.beans.factory.annotation.Autowired

@Integration
@Rollback
class TransactionIntSpec extends Specification {

    @Autowired
    FooService service

    void "test transaction rollback"() {
        given: 'bar service throws exception'
        service = Mock(FooService) {
            serviceBar() >> { throw new Exception() }
        }

        when:
        service.serviceMethod()

        then:
        !Foo.count() && !Bar.count()
    }

    void "test transaction successfull"() {
        when:
        service.serviceMethod()

        then:
        Foo.count() && Bar.count()
    }
}

where Foo and Bar are just simple domain classes and the service class would look like:

package com.example

import grails.transaction.Transactional

@Transactional
class FooService {

    def serviceMethod() {
        serviceFoo()
        serviceBar()
    }

    def serviceFoo() {
        new Foo().save(flush: true, failOnError: true)
    }

    def serviceBar() {
        new Bar().save(flush: true, failOnError: true)
    }
}

Tested in Grails 3.0.2