import ballerina/io;
import ballerina/h2;
import ballerina/sql;
h2:Client testDB = new ({
    path: "./local-transactions/",
    name: "Testdb",
    username: "test",
    password: "test",
    poolOptions: { maximumPoolSize: 5 }
});public function main() {
    var ret = testDB->update("CREATE TABLE CUSTOMER (ID INTEGER, NAME
                              VARCHAR(30))");
    handleUpdate(ret, "Create CUSTOMER table");    ret = testDB->update("CREATE TABLE SALARY (ID INTEGER, MON_SALARY FLOAT)");
    handleUpdate(ret, "Create SALARY table");
    transaction with retries = 4 {
        ret = testDB->update("INSERT INTO CUSTOMER(ID,NAME)
                                     VALUES (1, 'Anne')");
        ret = testDB->update("INSERT INTO SALARY (ID, MON_SALARY)
                                 VALUES (1, 2500)");
        if (ret is sql:UpdateResult) {
            io:println("Inserted count: " + ret.updatedRowCount);
            if (ret.updatedRowCount == 0) {
                abort;
            }
        } else {
            retry;
        }
    } onretry {
        io:println("Retrying transaction");
    } committed {
        io:println("Transaction committed");
    } aborted {
        io:println("Transaction aborted");
    }
    ret = testDB->update("DROP TABLE CUSTOMER");
    handleUpdate(ret, "Drop table CUSTOMER");    ret = testDB->update("DROP TABLE SALARY");
    handleUpdate(ret, "Drop table SALARY");
    var stopRet = testDB.stop();
    if (stopRet is error) {
        io:println(stopRet.detail().message);
    }
}
function handleUpdate(sql:UpdateResult|error returned, string message) {
    if (returned is sql:UpdateResult) {
        io:println(message + " status: " + returned.updatedRowCount);
    } else {
        io:println(message + " failed: " + returned.reason());
    }
}

Local Transactions

A Ballerina transaction is a series of data manipulation statements that must either fully complete or fully fail, thereby leaving the system in a consistent state. This sample uses H2 database, which is created when running the sample. Prior to running this sample, change the DB connection properties as required. Ballerina transactions are at experimental stage, please use –experimental flag to enable them.

import ballerina/io;
import ballerina/h2;
import ballerina/sql;
h2:Client testDB = new ({
    path: "./local-transactions/",
    name: "Testdb",
    username: "test",
    password: "test",
    poolOptions: { maximumPoolSize: 5 }
});

Create an endpoint for H2 database. Change the DB details before running the sample.

public function main() {
    var ret = testDB->update("CREATE TABLE CUSTOMER (ID INTEGER, NAME
                              VARCHAR(30))");
    handleUpdate(ret, "Create CUSTOMER table");

Create the tables required for the transaction.

    ret = testDB->update("CREATE TABLE SALARY (ID INTEGER, MON_SALARY FLOAT)");
    handleUpdate(ret, "Create SALARY table");
    transaction with retries = 4 {

Here is the transaction block. Any transacted action within the transaction block may return errors like backend DB errors, connection pool errors, etc. User can decide whether to abort or retry based on the error returned. If you do not explicitly abort or retry on a returned error, transaction will be automatically retried until the retry count is reached and aborted. The retry count that is given with retries is the number of times the transaction is retried before aborting it. By default, a transaction is tried three times before aborting. Only integer literals or constants are allowed for retry count.

        ret = testDB->update("INSERT INTO CUSTOMER(ID,NAME)
                                     VALUES (1, 'Anne')");

This is the first remote function participant in the transaction.

        ret = testDB->update("INSERT INTO SALARY (ID, MON_SALARY)
                                 VALUES (1, 2500)");
        if (ret is sql:UpdateResult) {
            io:println("Inserted count: " + ret.updatedRowCount);

This is the second remote function participant in the transaction.

            if (ret.updatedRowCount == 0) {
                abort;
            }
        } else {

If the transaction is forced to abort, it will roll back the transaction and exit the transaction block without retrying.

            retry;
        }

If the transaction is forced to retry, it will roll back the transaction, go to the onretry block, and retry from the beginning until the defined retry count is reached.

    } onretry {

The end curly bracket marks the end of the transaction, and the transaction will be committed or rolled back at this point.

        io:println("Retrying transaction");
    } committed {

The onretry block will be executed whenever the transaction is retried until it reaches the retry count. A transaction could be retried if it fails due to an exception or throw statement, or from an explicit retry statement.

        io:println("Transaction committed");
    } aborted {

Any action that needs to perform once the transaction is committed should go here.

        io:println("Transaction aborted");
    }

Any action that needs to perform if the transaction is aborted should go here.

    ret = testDB->update("DROP TABLE CUSTOMER");
    handleUpdate(ret, "Drop table CUSTOMER");

Drop the tables.

    ret = testDB->update("DROP TABLE SALARY");
    handleUpdate(ret, "Drop table SALARY");
    var stopRet = testDB.stop();
    if (stopRet is error) {
        io:println(stopRet.detail().message);
    }
}

Close the connection pool.

function handleUpdate(sql:UpdateResult|error returned, string message) {
    if (returned is sql:UpdateResult) {
        io:println(message + " status: " + returned.updatedRowCount);
    } else {
        io:println(message + " failed: " + returned.reason());
    }
}

Function to handle return of the update operation.

# To run this sample, navigate to the directory that contains the
# `.bal` file and execute the `ballerina run` command.
$ ballerina run  --experimental local_transactions.bal
[ballerina/http] started HTTP/WS endpoint 172.17.0.1:49479
Create CUSTOMER table status: 0
Create SALARY table status: 0
2018-12-04 20:16:37,124 INFO  [ballerina/log] - Created transaction: 1fba678d-d677-4301-bd1b-a7fa60664f6a
Inserted count: 1
2018-12-04 20:16:37,137 INFO  [ballerina/log] - Running 2-phase commit for transaction: 1fba678d-d677-4301-bd1b-a7fa60664f6a:$anon$.$1
Transaction committed
Drop table CUSTOMER status: 0
Drop table SALARY status: 0