17 January 2016
As we know, Grails services by default have automatic transaction management such as propagation and so on. By setting transactional to false you disable automatic Spring managed transaction demarcation for every method in the service. When is set to the true, The propagation level of the transaction is by default PROPAGATION_REQUIRED. Also we can use the @ Transactional and @ NotTransactional annotations.
What is a transaction? In simple terms, a transaction is:
But, what is happening in our database when we use transactions? The purpose of this post is to present some small code examples and the behavior at database level. There are some requirements for them to run the following examples:
First, we can start creating a Domain Class and a Service to examine how a transaction works:
class Person {
String name
}
@Transactional
class PersonService {
def save() {
new Person(name: "Roberto").save()
new Person(name: "Raj").save()
}
}
Now, if we invoke the save method, we can see the following in our database log.
140605 8:42:42 11 Query SET autocommit=0
11 Query insert into person (version, name) values (0, 'Roberto')
11 Query insert into person (version, name) values (0, 'Raj')
11 Query commit
11 Query SET autocommit=1
As you can see, is setting the autocommit to 0, inserting, commit, set autocommit 1. This is what we are expecting to do, this is the way we handle transactions.
Now let's see what happens when we add another method:
@Transactional
class PersonService {
def save() {
new Person(name: "Roberto").save()
new Person(name: "Raj").save()
anotherSave()
}
def anotherSave() {
new Person(name: "anotherPerson").save()
}
}
If we invoke the save method again we will get this result:
140605 8:48:58 11 Query SET autocommit=0
11 Query insert into person (version, name) values (0, 'Roberto')
11 Query insert into person (version, name) values (0, 'Raj')
11 Query insert into person (version, name) values (0, 'anotherPerson')
11 Query commit
11 Query SET autocommit=1
By default, the Grails transactions have REQUIRED propagation, and in this case the 'anotherSave' method will join the current transaction.
But what if we need to run the 'anotherMethod' in another transaction? Well, we can just change the transaction's propagation to a REQUIRES_NEW. This will look like this:
@Transactional
class PersonService {
def save() {
new Person(name: "Roberto").save()
new Person(name: "Raj").save()
anotherSave()
}
@Transactional(propagation = Propagation.REQUIRES_NEW)
def anotherSave() {
new Person(name: "anotherPerson").save()
}
}
Again, we can invoke the save method and see the results:
140605 8:58:41 11 Query SET autocommit=0
11 Query insert into person (version, name) values (0, 'Roberto')
11 Query insert into person (version, name) values (0, 'Raj')
10 Query SET autocommit=0
10 Query insert into person (version, name) values (0, 'anotherPerson')
10 Query commit
10 Query SET autocommit=1
11 Query commit
11 Query SET autocommit=1
At first glance we can see the change in autocommit but the most important thing are the numbers (10 and 11). These numbers represent the thread id, with this we know that they are running in another transaction.
Now, let's see what happens when a RuntimeException occurs in save method after invoking anotherSave. If you want, truncate our table after that, can change our code to this:
@Transactional
class PersonService {
def save() {
new Person(name: "Roberto").save()
new Person(name: "Raj").save()
anotherSave()
throw new RuntimeException("oh!")
}
@Transactional(propagation = Propagation.REQUIRES_NEW)
def anotherSave() {
new Person(name: "anotherPerson").save()
}
}
If we invoke the save method we will get the following in our database log:
140605 9:06:19 11 Query SET autocommit=0
11 Query insert into person (version, name) values (0, 'Roberto')
11 Query insert into person (version, name) values (0, 'Raj')
10 Query SET autocommit=0
10 Query insert into person (version, name) values (0, 'anotherPerson')
10 Query commit
10 Query SET autocommit=1
11 Query rollback
11 Query SET autocommit=1
Ok, we know we have two transactions, let's make query to the database.
mysql> select * from person;
+----+---------+---------------+
| id | version | name |
+----+---------+---------------+
| 3 | 0 | anotherPerson |
+----+---------+---------------+
1 row in set (0.01 sec)
mysql>
As you can see, we have a new record in our database, this was generated by the method anotherSave. This is because we are running this statement in another transaction.
IMPORTANT: automatic rollback occurs if a method throws a runtime exception (i.e. one that extends RuntimeException) or an Error. Checked exceptions do not roll back transactions.
Ok, by now you know that the transaction disables autocommit to execute one or more statements and then do the commit.
Now imagine a scenario in which you require that the statements of anotherSave should be with auto-commit (MySQL has autocommit enabled by default). Let's take a look on this... first, we will put a few examples of possible ideas that won't work:
FAIL: Using @NotTransactional annotation
@Transactional
class PersonService {
def save() {
new Person(name: "Roberto").save()
anotherSave()
new Person(name: "Raj").save()
}
@NotTransactional
def anotherSave() {
new Person(name: "anotherPerson").save()
}
}
With this approach our database log will be:
140605 9:16:34 11 Query SET autocommit=0
11 Query insert into person (version, name) values (0, 'Roberto')
11 Query insert into person (version, name) values (0, 'anotherPerson')
11 Query insert into person (version, name) values (0, 'Raj')
11 Query commit
11 Query SET autocommit=1
Look at this... is under the same thread. So this option is discarded.
FAIL: Using Groovy SQL without change the propagation
Well... maybe we can think something like "If MySQL sets autocommit by default we can use SQL with GroovySQL and done". The service can be like this:
@Transactional
class PersonService {
def save() {
new Person(name: "Roberto").save()
anotherSave()
new Person(name: "Raj").save()
}
def sessionFactory
def anotherSave() {
def sql = new Sql(sessionFactory.currentSession.connection())
sql.execute("INSERT INTO `test`.`person` (`version`, `name`) VALUES ('0', 'anotherPerson')")
}
}
Ok... run this and you will see all in one transaction:
140605 9:21:15 21 Query SET autocommit=0
21 Query insert into person (version, name) values (0, 'Roberto')
21 Query INSERT INTO `test`.`person` (`version`, `name`) VALUES ('0', 'anotherPerson')
21 Query insert into person (version, name) values (0, 'Raj')
21 Query commit
21 Query SET autocommit=1
So this is not working as we expected... oh but maybe "we can just change the connection to autocommit".
FAIL: Using Groovy SQL and change the connection to autocommit
@Transactional
class PersonService {
def save() {
new Person(name: "Roberto").save()
anotherSave()
new Person(name: "Raj").save()
}
def sessionFactory
def anotherSave() {
def sql = new Sql(sessionFactory.currentSession.connection())
sql.connection.setAutoCommit(true)
sql.execute("INSERT INTO `test`.`person` (`version`, `name`) VALUES ('0', 'anotherPerson')")
sql.connection.setAutoCommit(false)
}
}
Execute this and look:
140605 9:22:47 21 Query SET autocommit=0
21 Query insert into person (version, name) values (0, 'Roberto')
21 Query SET autocommit=1
21 Query INSERT INTO `test`.`person` (`version`, `name`) VALUES ('0', 'anotherPerson')
21 Query SET autocommit=0
21 Query insert into person (version, name) values (0, 'Raj')
21 Query commit
21 Query SET autocommit=1
Oh! is changing the autocommit but wait... is the same thread. What is happening with the first query. Of course this seems to be ok because we are inserting everything and the code is not throwing a RuntimeException or Error. Lets add a RuntimeException and run it again. For this, we will truncate our table.
@Transactional
class PersonService {
def save() {
new Person(name: "Roberto").save()
anotherSave()
new Person(name: "Raj").save()
throw new RuntimeException("Oh!")
}
def sessionFactory
def anotherSave() {
def sql = new Sql(sessionFactory.currentSession.connection())
sql.connection.setAutoCommit(true)
sql.execute("INSERT INTO `test`.`person` (`version`, `name`) VALUES ('0', 'anotherPerson')")
sql.connection.setAutoCommit(false)
}
}
Execute this and you will see the same query log but look at your table data:
mysql> select * from person;
+----+---------+---------------+
| id | version | name |
+----+---------+---------------+
| 1 | 0 | Roberto |
| 2 | 0 | anotherPerson |
+----+---------+---------------+
2 rows in set (0.00 sec)
mysql>
Yes we set the autocommit to true but we were under the same transaction so our first query was commited too and we don't want this.
This is where we can use another propagation. Let's try it with NOTSUPPORTED and use GORM again. NOTSUPPORTED execute non-transactionally, suspend the current transaction if one exists.
OK: Use NOT_SUPPORTED propagation
@Transactional
class PersonService {
def save() {
new Person(name: "Roberto").save()
anotherSave()
new Person(name: "Raj").save()
throw new RuntimeException("Oh!")
}
@Transactional(propagation = Propagation.NOT_SUPPORTED)
def anotherSave() {
new Person(name: "another Person").save()
}
}
Let's try this piece of code. We will truncate our table before that.
140605 9:29:14 21 Query SET autocommit=0
21 Query insert into person (version, name) values (0, 'Roberto')
20 Query insert into person (version, name) values (0, 'another Person')
21 Query insert into person (version, name) values (0, 'Raj')
21 Query rollback
21 Query SET autocommit=1
Take a look on the thread ids, our another person statement is running in another thread and using the autocommit behaviour from MySQL. Now, look at our data
mysql> select * from person;
+----+---------+----------------+
| id | version | name |
+----+---------+----------------+
| 2 | 0 | another Person |
+----+---------+----------------+
1 row in set (0.00 sec)
mysql>
We only have the record that we want and because we had an exception, the transaction in the save method became a rollback. This is a nice approach.
IMPORTANT: We know that maybe is not common to go under an auto-commit rule but you never know maybe tomorrow you will need it
To finish this small post, here is a list of the available propagations:
we deal with important data to the business, so we must pay close attention to avoid any data loss problem while running transactions
These examples were developed along with Raj Govindarajan.
Hope this can help you in the future.