Using 'sequence' generator in database mapping causes unexpected COMMIT in transaction

[vdusa]

Steps to Reproduce

Let's have a domain using

class MyDomain {
	...
	static mapping = {
		id generator: 'sequence'
	}
	...
}

Since generation of each id causes commit in current transaction, following code behaves unexpectedly:

MyDomain.withTransaction { status ->
	def d1 = new MyDomain(someProperty: 'Foo')
	d1.save(flush: true)
	
	def d2 = new MyDomain(someProperty: 'Bar')
	d2.save(flush: true) <-- sequence id generator calls COMMIT, that commits also d1
	
	status.setRollbackOnly() <-- just d2 is rolled back
}

Expected Behaviour

Both domains d1 and d2 should be rolled back at the end of the transaction.

Actual Behaviour

MySQL: Since we use MySQL, hibernate uses org.hibernate.id.enhanced.TableGenerator as fallback. As soon as this sequence generator updates and commits next_val in hibernate_sequences table, the MyDomain d1 is also commited during generating of the id of d2, so the rollback at the end of the transaction rolles back just d2.

Postgres: We also tried to use Postgres database, where no hibernate_sequences table has been created. The unexpected commit is performed same as in MySQL case.

H2: It works correctly when H2 is used.

Remark: We debugged the code also with our own generator derived from org.hibernate.id.enhanced.TableGenerator calling just super.generate() method and it seems as if the generator doesn't use new connection while updating next_val in hibernate_sequences table.

Environment Information

• Operating System: Windows 10
• Grails Version: 3.3.9 (tested also in also 2.5.6)
• JDK Version: TODO
• Container Version (If Applicable): Tomcat bundled with Grails
• MySQL, Postgres

Example Application

https://github.com/jhron/GrailsSequenceTest Just update dataSource settings in application.yml and bootRun. Then call http://localhost:8080/person/create