The “Single Application” Pattern

The “Single Application” Pattern
Prev	Chapter 8. Developing Application Programs using XTA (XA Transaction API) interface	Next

The pattern in brief:
Architecture:	see Figure 8.2, “Single application layout”
Number of Application Programs:	exactly 1
Number of Resource Managers:	many, if 1 single phase commit will be used
Number of Branches in the Gloabal Transaction:	exactly 1
Concurrency among Application Programs:	not applicable

Description

This is a traditional Distributed Transaction Processing pattern: a single Application Program uses two or more Resource Managers and performs a transaction that change the state of both. Here is a list of examples:

RM1 and RM2 are two different databases, for example PostgreSQL and MySQL (or MariaDB): the Application Program moves a record from RM1 (PostgreSQL) to RM2 (MySQL)
RM1 and RM2 are a messaging system and a database, for example IBM MQ and Oracle Databae Server: the Application Program get a message from a queue, insert a row in a table, remove the message from the queue
RM1 and RM2 are two different types of database and the Application Program inserts exactly the same data in both, maybe using a different format

The pattern can be implemented even using the standard TX Transaction Demarcation interface, but XTA is much more object oriented.

How it works

Figure 8.5. Simplified sequence diagram for the “Single Application Program” Pattern

The above figure shows the logic necessary to build this type of application; it's not a formally correct UML sequence diagram (all the XTA objects are in the same lifeline), but it should be clear enough to understand the proper operations sequence:

The Application Program (AP) creates the native objects/connections/handles ^[47] that are necessary to operate with the Resource Managers ^[48]
The AP creates some XTA objects: a TransactionManager, two XaResource and one Transaction
The AP “enlists” the XA resources that must be controlled by the transaction using EnlistResource() method
The AP uses the Open() method to initialize the XaResource objects
The AP uses the Start() method to start a new XA distributed transaction
The AP interacts with the Resource Managers, using the native objects/connections/handles to operate (“doSomething” method in the diagram)
The AP uses the Commit() method to commit the distributed transaction, or the Rollback() method to rollback all the changes since Start()
The AP cleans-up the environment

Note

The dashed red rectangle highlights the XA global transaction.

Note

The methods listed in the above description must be considered “pseudo-code”: the real name and signature is language dependent. As an example,

tm = new TransactionManager()

translates to

tm = xta_transaction_manager_new()

and

tx.Commit()

translates to

xta_transaction_commit(tx, FALSE)

in C.

An example using the C language

The supplied example (example_xta_sa01.c) uses PostgreSQL in the role of “Resource Manager 1” and MySQL (or MariaDB) in the role of “Resource Manager 2”; please refer to the instructions explained :

in the section called “MySQL Configuration” to set-up a running environment for MySQL server
in the section called “PostgreSQL Configuration” to set-up a running environment for PostgreSQL server
in the section called “Starting the state server (lixad)” to start up the LIXA state server

Create a working directory in a place you are comfortable with:

[Shell terminal session]
tiian@ubuntu1404-64:~$ cd tiian@ubuntu1404-64:~$ mkdir tmp tiian@ubuntu1404-64:~$ cd tmp tiian@ubuntu1404-64:~/tmp$

Copy file example_xta_sa01.c in your working dir:

[Shell terminal session]
tiian@ubuntu:~/tmp$ cp /opt/lixa/share/doc/lixa-X.Y.Z/examples/xta/example_xta_sa01.c .

Substitute “X.Y.Z” with the actual version of the software you installed.

Compile and link the C example program:

[Shell terminal session]
tiian@ubuntu1404-64:~/tmp$ . /opt/lixa/bin/lixa_env.sh tiian@ubuntu1404-64:~/tmp$ gcc example_xta_sa01.c $(lixa-config -x -c -f -l -d) -lpq $(mysql_config --libs_r) -o example_xta_sa01

If the previous steps worked for you, you should have an executable file of name example_xta_sa01 in your current directory:

[Shell terminal session]
tiian@ubuntu1404-64:/tmp$ ls -l total 32 -rwxrwxr-x 1 tiian tiian 18603 mar 20 22:56 example_xta_sa01 -rw-r--r-- 1 tiian tiian 8302 mar 20 22:50 example_xta_sa01.c

The example program accepts two arguments:

commit (or rollback): use “1” if you want to perform a global commit or “0” if you want to perform a global rollback
insert (or delete): use “1” if you want to insert rows in databases or “0” if you want to delete rows from databases

Open three terminal sessions: one for example execution,

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ ls -la total 40 drwxrwxr-x 2 tiian tiian 4096 mar 20 23:13 . drwxr-xr-x 12 tiian tiian 4096 mar 21 22:17 .. -rwxrwxr-x 1 tiian tiian 18603 mar 20 23:13 example_xta_sa01 -rw-r--r-- 1 tiian tiian 8302 mar 20 23:08 example_xta_sa01.c

[Shell terminal session - AP]

tiian@ubuntu1404-64:~/tmp$ ls -la
total 40
drwxrwxr-x  2 tiian tiian  4096 mar 20 23:13 .
drwxr-xr-x 12 tiian tiian  4096 mar 21 22:17 ..
-rwxrwxr-x  1 tiian tiian 18603 mar 20 23:13 example_xta_sa01
-rw-r--r--  1 tiian tiian  8302 mar 20 23:08 example_xta_sa01.c

one for PostgreSQL queries

[Shell terminal session - PostgreSQL]
tiian@ubuntu1404-64:~$ psql testdb psql (9.3.22) Type "help" for help. testdb=>

and one for MySQL queries

[Shell terminal session - MySQL]
tiian@ubuntu1404-64:~$ mysql -h localhost -u lixa lixa Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 36 Server version: 5.5.59-0ubuntu0.14.04.1 (Ubuntu) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

[Shell terminal session - MySQL]

tiian@ubuntu1404-64:~$ mysql -h localhost -u lixa lixa
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.5.59-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

set LIXA_PROFILE environment variable to XTA_DYN a profile without static Resource Managers defined in lixac_conf.xml:

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ export LIXA_PROFILE=XTA_DYN tiian@ubuntu1404-64:~/tmp$ echo $LIXA_PROFILE XTA_DYN

insert rows in tables and commit:

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ ./example_xta_sa01 1 1 PostgreSQL, executing >INSERT INTO authors VALUES(1921, 'Rigoni Stern', 'Mario')< MySQL, executing >INSERT INTO authors VALUES(1919, 'Levi', 'Primo')< tiian@ubuntu1404-64:~/tmp$

[Shell terminal session - AP]

tiian@ubuntu1404-64:~/tmp$ ./example_xta_sa01 1 1
PostgreSQL, executing >INSERT INTO authors VALUES(1921, 'Rigoni Stern', 'Mario')<
MySQL, executing >INSERT INTO authors VALUES(1919, 'Levi', 'Primo')<
tiian@ubuntu1404-64:~/tmp$

check PostgreSQL table content:

[Shell terminal session - PostgreSQL]
testdb=> select * from authors; id \| last_name \| first_name ------+--------------+------------ 1921 \| Rigoni Stern \| Mario (1 row) testdb=>

check MySQL table content:

[Shell terminal session - MySQL]
mysql> select * from authors; +------+-----------+------------+ \| id \| last_name \| first_name \| +------+-----------+------------+ \| 1919 \| Levi \| Primo \| +------+-----------+------------+ 1 row in set (0.00 sec) mysql>

[Shell terminal session - MySQL]

mysql> select * from authors;
+------+-----------+------------+
| id   | last_name | first_name |
+------+-----------+------------+
| 1919 | Levi      | Primo      |
+------+-----------+------------+
1 row in set (0.00 sec)

mysql>

delete rows from tables, but rollback:

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ ./example_xta_sa01 0 0 PostgreSQL, executing >DELETE FROM authors WHERE id=1921< MySQL, executing >DELETE FROM authors WHERE id=1919< tiian@ubuntu1404-64:~/tmp$

check PostgreSQL table content:

[Shell terminal session - PostgreSQL]
testdb=> select * from authors; id \| last_name \| first_name ------+--------------+------------ 1921 \| Rigoni Stern \| Mario (1 row) testdb=>

check MySQL table content:

[Shell terminal session - MySQL]
mysql> select * from authors; +------+-----------+------------+ \| id \| last_name \| first_name \| +------+-----------+------------+ \| 1919 \| Levi \| Primo \| +------+-----------+------------+ 1 row in set (0.00 sec) mysql>

[Shell terminal session - MySQL]

mysql> select * from authors;
+------+-----------+------------+
| id   | last_name | first_name |
+------+-----------+------------+
| 1919 | Levi      | Primo      |
+------+-----------+------------+
1 row in set (0.00 sec)

mysql>

delete rows and commit:

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ ./example_xta_sa01 1 0 PostgreSQL, executing >DELETE FROM authors WHERE id=1921< MySQL, executing >DELETE FROM authors WHERE id=1919< tiian@ubuntu1404-64:~/tmp$

check PostgreSQL table content:

[Shell terminal session - PostgreSQL]
testdb=> select * from authors; id \| last_name \| first_name ----+-----------+------------ (0 rows) testdb=>

check MySQL table content:

[Shell terminal session - MySQL]
mysql> select * from authors; Empty set (0.00 sec) mysql>

Source Code

Source code for program example_xta_sa01.c is installed in directory /opt/lixa/share/doc/lixa-X.Y.Z/examples/xta and is available on GitHub. Source code is fully commented for readability.

An example using the C++ language

The supplied example (example_xta_sa11.cpp) uses PostgreSQL in the role of “Resource Manager 1” and MySQL (or MariaDB) in the role of “Resource Manager 2”; please refer to the instructions explained:

in the section called “MySQL Configuration” to set-up a running environment for MySQL server
in the section called “PostgreSQL Configuration” to set-up a running environment for PostgreSQL server
in the section called “Starting the state server (lixad)” to start up the LIXA state server

Create a working directory in a place you are comfortable with:

[Shell terminal session]
tiian@ubuntu1404-64:~$ cd tiian@ubuntu1404-64:~$ mkdir tmp tiian@ubuntu1404-64:~$ cd tmp tiian@ubuntu1404-64:~/tmp$

Copy file example_xta_sa11.cpp in your working dir:

[Shell terminal session]
tiian@ubuntu:~/tmp$ cp /opt/lixa/share/doc/lixa-X.Y.Z/examples/xta/cpp/example_xta_sa11.cpp .

Substitute “X.Y.Z” with the actual version of the software you installed.

Compile and link the C++ example program:

[Shell terminal session]
tiian@ubuntu1404-64:~/tmp$ . /opt/lixa/bin/lixa_env.sh tiian@ubuntu1404-64:~/tmp$ g++ example_xta_sa11.cpp $(lixa-config -c -f -l -d --xta --language-cpp) -lpq $(mysql_config --libs_r) -o example_xta_sa11

[Shell terminal session]

tiian@ubuntu1404-64:~/tmp$ . /opt/lixa/bin/lixa_env.sh 
tiian@ubuntu1404-64:~/tmp$ g++ example_xta_sa11.cpp $(lixa-config -c -f -l -d --xta --language-cpp) -lpq $(mysql_config --libs_r) -o example_xta_sa11

If the previous steps worked for you, you should have an executable file of name example_xta_sa11 in your current directory:

[Shell terminal session]
tiian@ubuntu1404-64:~/tmp$ ls -l total 36 -rwxrwxr-x 1 tiian tiian 25613 ago 24 22:19 example_xta_sa11 -rw-r--r-- 1 tiian tiian 7748 ago 24 22:19 example_xta_sa11.cpp

The example program accepts two arguments:

commit (or rollback): use “1” if you want to perform a global commit or “0” if you want to perform a global rollback
insert (or delete): use “1” if you want to insert rows in databases or “0” if you want to delete rows from databases

Open three terminal sessions: one for example execution,

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ ls -la total 44 drwxrwxr-x 2 tiian tiian 4096 ago 24 22:19 . drwxr-xr-x 12 tiian tiian 4096 ago 24 22:02 .. -rwxrwxr-x 1 tiian tiian 25613 ago 24 22:19 example_xta_sa11 -rw-r--r-- 1 tiian tiian 7748 ago 24 22:19 example_xta_sa11.cpp

[Shell terminal session - AP]

tiian@ubuntu1404-64:~/tmp$ ls -la
total 44
drwxrwxr-x  2 tiian tiian  4096 ago 24 22:19 .
drwxr-xr-x 12 tiian tiian  4096 ago 24 22:02 ..
-rwxrwxr-x  1 tiian tiian 25613 ago 24 22:19 example_xta_sa11
-rw-r--r--  1 tiian tiian  7748 ago 24 22:19 example_xta_sa11.cpp

one for PostgreSQL queries

[Shell terminal session - PostgreSQL]
tiian@ubuntu1404-64:~$ psql testdb psql (9.3.23) Type "help" for help. testdb=>

and one for MySQL queries

[Shell terminal session - MySQL]
tiian@ubuntu1404-64:~$ mysql -h localhost -u lixa lixa Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 44 Server version: 5.5.61-0ubuntu0.14.04.1 (Ubuntu) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

[Shell terminal session - MySQL]

tiian@ubuntu1404-64:~$ mysql -h localhost -u lixa lixa
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 44
Server version: 5.5.61-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

set LIXA_PROFILE environment variable to XTA_DYN a profile without static Resource Managers defined in lixac_conf.xml:

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ export LIXA_PROFILE=XTA_DYN tiian@ubuntu1404-64:~/tmp$ echo $LIXA_PROFILE XTA_DYN

insert rows in tables and commit:

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ ./example_xta_sa11 1 1 PostgreSQL, executing >INSERT INTO authors VALUES(1921, 'Rigoni Stern', 'Mario')< MySQL, executing >INSERT INTO authors VALUES(1919, 'Levi', 'Primo')< tiian@ubuntu1404-64:~/tmp$

[Shell terminal session - AP]

tiian@ubuntu1404-64:~/tmp$ ./example_xta_sa11 1 1
PostgreSQL, executing >INSERT INTO authors VALUES(1921, 'Rigoni Stern', 'Mario')<
MySQL, executing >INSERT INTO authors VALUES(1919, 'Levi', 'Primo')<
tiian@ubuntu1404-64:~/tmp$

check PostgreSQL table content:

[Shell terminal session - PostgreSQL]
testdb=> select * from authors; id \| last_name \| first_name ------+--------------+------------ 1921 \| Rigoni Stern \| Mario (1 row) testdb=>

check MySQL table content:

[Shell terminal session - MySQL]
mysql> select * from authors; +------+-----------+------------+ \| id \| last_name \| first_name \| +------+-----------+------------+ \| 1919 \| Levi \| Primo \| +------+-----------+------------+ 1 row in set (0.00 sec) mysql>

[Shell terminal session - MySQL]

mysql> select * from authors;
+------+-----------+------------+
| id   | last_name | first_name |
+------+-----------+------------+
| 1919 | Levi      | Primo      |
+------+-----------+------------+
1 row in set (0.00 sec)

mysql>

delete rows from tables, but rollback:

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ ./example_xta_sa11 0 0 PostgreSQL, executing >DELETE FROM authors WHERE id=1921< MySQL, executing >DELETE FROM authors WHERE id=1919< tiian@ubuntu1404-64:~/tmp$

check PostgreSQL table content:

[Shell terminal session - PostgreSQL]
testdb=> select * from authors; id \| last_name \| first_name ------+--------------+------------ 1921 \| Rigoni Stern \| Mario (1 row) testdb=>

check MySQL table content:

[Shell terminal session - MySQL]
mysql> select * from authors; +------+-----------+------------+ \| id \| last_name \| first_name \| +------+-----------+------------+ \| 1919 \| Levi \| Primo \| +------+-----------+------------+ 1 row in set (0.00 sec) mysql>

[Shell terminal session - MySQL]

mysql> select * from authors;
+------+-----------+------------+
| id   | last_name | first_name |
+------+-----------+------------+
| 1919 | Levi      | Primo      |
+------+-----------+------------+
1 row in set (0.00 sec)

mysql>

delete rows and commit:

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ ./example_xta_sa11 1 0 PostgreSQL, executing >DELETE FROM authors WHERE id=1921< MySQL, executing >DELETE FROM authors WHERE id=1919< tiian@ubuntu1404-64:~/tmp$

check PostgreSQL table content:

[Shell terminal session - PostgreSQL]
testdb=> select * from authors; id \| last_name \| first_name ----+-----------+------------ (0 rows) testdb=>

check MySQL table content:

[Shell terminal session - MySQL]
mysql> select * from authors; Empty set (0.00 sec) mysql>

Source Code

Source code for program example_xta_sa11.cpp is installed in directory /opt/lixa/share/doc/lixa-X.Y.Z/examples/xta/cpp and is available on GitHub. Source code is fully commented for readability.

An example using the Java language

The supplied example (ExampleXtaSA31.java) uses PostgreSQL in the role of “Resource Manager 1” and MySQL (or MariaDB) in the role of “Resource Manager 2”; please refer to the instructions explained:

in the section called “MySQL Configuration” to set-up a running environment for MySQL server
in the section called “PostgreSQL Configuration” to set-up a running environment for PostgreSQL server
in the section called “Starting the state server (lixad)” to start up the LIXA state server

Create a working directory in a place you are comfortable with:

[Shell terminal session]
tiian@ubuntu1404-64:~$ cd tiian@ubuntu1404-64:~$ mkdir tmp tiian@ubuntu1404-64:~$ cd tmp tiian@ubuntu1404-64:~/tmp$

Copy file ExampleXtaSA31.java in your working dir:

[Shell terminal session]
tiian@ubuntu:~/tmp$ cp /opt/lixa/share/doc/lixa-X.Y.Z/examples/xta/java/ExampleXtaSA31.java .

Substitute “X.Y.Z” with the actual version of the software you installed.

Use the proper paths for MySQL and PostgreSQL jars, then compile the Java example program with something like:

[Shell terminal session]
tiian@ubuntu1404-64:~/tmp$ javac -cp /opt/lixa/share/lixa/java/xta.jar:/usr/share/java/mysql.jar:/opt/postgresql/postgresql.jar ExampleXtaSA31.java

If the previous steps worked for you, you should have a class file of name ExampleXtaSA31.class in your current directory:

[Shell terminal session]
tiian@ubuntu1404-64:~/tmp$ ls -l total 16 -rw-rw-r-- 1 tiian tiian 4169 gen 13 22:24 ExampleXtaSA31.class -rw-r--r-- 1 tiian tiian 8065 gen 13 22:22 ExampleXtaSA31.java

The example program accepts two arguments:

commit (or rollback): use “1” if you want to perform a global commit or “0” if you want to perform a global rollback
insert (or delete): use “1” if you want to insert rows in databases or “0” if you want to delete rows from databases

Open three terminal sessions: one for example execution,

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ ls -la total 24 drwxrwxr-x 2 tiian tiian 4096 gen 13 22:24 . drwxr-xr-x 17 tiian tiian 4096 gen 13 22:23 .. -rw-rw-r-- 1 tiian tiian 4169 gen 13 22:24 ExampleXtaSA31.class -rw-r--r-- 1 tiian tiian 8065 gen 13 22:22 ExampleXtaSA31.java

[Shell terminal session - AP]

tiian@ubuntu1404-64:~/tmp$ ls -la
total 24
drwxrwxr-x  2 tiian tiian 4096 gen 13 22:24 .
drwxr-xr-x 17 tiian tiian 4096 gen 13 22:23 ..
-rw-rw-r--  1 tiian tiian 4169 gen 13 22:24 ExampleXtaSA31.class
-rw-r--r--  1 tiian tiian 8065 gen 13 22:22 ExampleXtaSA31.java

one for PostgreSQL queries

[Shell terminal session - PostgreSQL]
tiian@ubuntu1404-64:~$ psql testdb psql (9.3.24) Type "help" for help. testdb=>

and one for MySQL queries

[Shell terminal session - MySQL]
tiian@ubuntu1404-64:~$ mysql -h localhost -u lixa lixa Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 44 Server version: 5.5.62-0ubuntu0.14.04.1 (Ubuntu) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

[Shell terminal session - MySQL]

tiian@ubuntu1404-64:~$ mysql -h localhost -u lixa lixa
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 44
Server version: 5.5.62-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

set LIXA_PROFILE environment variable to XTA_DYN a profile without static Resource Managers defined in lixac_conf.xml:

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ export LIXA_PROFILE=XTA_DYN tiian@ubuntu1404-64:~/tmp$ echo $LIXA_PROFILE XTA_DYN

insert rows in tables and commit:

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ java -Djava.library.path=/opt/lixa/lib -cp /opt/lixa/share/lixa/java/xta.jar:/usr/share/java/mysql.jar:/opt/postgresql/postgresql.jar:. ExampleXtaSA31 1 1 PostgreSQL, executing >INSERT INTO authors VALUES(1921, 'Rigoni Stern', 'Mario')< MySQL, executing >INSERT INTO authors VALUES(1919, 'Levi', 'Primo')< tiian@ubuntu1404-64:~/tmp$

[Shell terminal session - AP]

tiian@ubuntu1404-64:~/tmp$ java -Djava.library.path=/opt/lixa/lib -cp /opt/lixa/share/lixa/java/xta.jar:/usr/share/java/mysql.jar:/opt/postgresql/postgresql.jar:. ExampleXtaSA31 1 1
PostgreSQL, executing >INSERT INTO authors VALUES(1921, 'Rigoni Stern', 'Mario')<
MySQL, executing >INSERT INTO authors VALUES(1919, 'Levi', 'Primo')<
tiian@ubuntu1404-64:~/tmp$

check PostgreSQL table content:

[Shell terminal session - PostgreSQL]
testdb=> select * from authors; id \| last_name \| first_name ------+--------------+------------ 1921 \| Rigoni Stern \| Mario (1 row) testdb=>

check MySQL table content:

[Shell terminal session - MySQL]
mysql> select * from authors; +------+-----------+------------+ \| id \| last_name \| first_name \| +------+-----------+------------+ \| 1919 \| Levi \| Primo \| +------+-----------+------------+ 1 row in set (0.00 sec) mysql>

[Shell terminal session - MySQL]

mysql> select * from authors;
+------+-----------+------------+
| id   | last_name | first_name |
+------+-----------+------------+
| 1919 | Levi      | Primo      |
+------+-----------+------------+
1 row in set (0.00 sec)

mysql>

delete rows from tables, but rollback:

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ java -Djava.library.path=/opt/lixa/lib -cp /opt/lixa/share/lixa/java/xta.jar:/usr/share/java/mysql.jar:/opt/postgresql/postgresql.jar:. ExampleXtaSA31 0 0 PostgreSQL, executing >DELETE FROM authors WHERE id=1921< MySQL, executing >DELETE FROM authors WHERE id=1919< tiian@ubuntu1404-64:~/tmp$

[Shell terminal session - AP]

tiian@ubuntu1404-64:~/tmp$ java -Djava.library.path=/opt/lixa/lib -cp /opt/lixa/share/lixa/java/xta.jar:/usr/share/java/mysql.jar:/opt/postgresql/postgresql.jar:. ExampleXtaSA31 0 0
PostgreSQL, executing >DELETE FROM authors WHERE id=1921<
MySQL, executing >DELETE FROM authors WHERE id=1919<
tiian@ubuntu1404-64:~/tmp$

check PostgreSQL table content:

[Shell terminal session - PostgreSQL]
testdb=> select * from authors; id \| last_name \| first_name ------+--------------+------------ 1921 \| Rigoni Stern \| Mario (1 row) testdb=>

check MySQL table content:

[Shell terminal session - MySQL]
mysql> select * from authors; +------+-----------+------------+ \| id \| last_name \| first_name \| +------+-----------+------------+ \| 1919 \| Levi \| Primo \| +------+-----------+------------+ 1 row in set (0.00 sec) mysql>

[Shell terminal session - MySQL]

mysql> select * from authors;
+------+-----------+------------+
| id   | last_name | first_name |
+------+-----------+------------+
| 1919 | Levi      | Primo      |
+------+-----------+------------+
1 row in set (0.00 sec)

mysql>

delete rows and commit:

[Shell terminal session - AP]

tiian@ubuntu1404-64:~/tmp$ java -Djava.library.path=/opt/lixa/lib -cp /opt/lixa/share/lixa/java/xta.jar:/usr/share/java/mysql.jar:/opt/postgresql/postgresql.jar:. ExampleXtaSA31 1 0
PostgreSQL, executing >DELETE FROM authors WHERE id=1921<
MySQL, executing >DELETE FROM authors WHERE id=1919<
tiian@ubuntu1404-64:~/tmp$

check PostgreSQL table content:

[Shell terminal session - PostgreSQL]
testdb=> select * from authors; id \| last_name \| first_name ----+-----------+------------ (0 rows) testdb=>

check MySQL table content:

[Shell terminal session - MySQL]
mysql> select * from authors; Empty set (0.00 sec) mysql>

Source Code

Source code for program ExampleXtaSA31.java is installed in directory /opt/lixa/share/doc/lixa-X.Y.Z/examples/xta/java and is available on GitHub. Source code is fully commented for readability.

An example using the Python language

The supplied example (example_xta_sa21.py) uses PostgreSQL in the role of “Resource Manager 1” and MySQL (or MariaDB) in the role of “Resource Manager 2”; please refer to the instructions explained :

in the section called “XTA Technology Matrix” to build and install the Python database drivers that can be used with XTA
in the section called “MySQL Configuration” to set-up a running environment for MySQL server
in the section called “PostgreSQL Configuration” to set-up a running environment for PostgreSQL server
in the section called “Starting the state server (lixad)” to start up the LIXA state server

The example can be used with Python 2 and Python 3.

Create a working directory in a place you are comfortable with:

[Shell terminal session]
tiian@ubuntu1404-64:~$ cd tiian@ubuntu1404-64:~$ mkdir tmp tiian@ubuntu1404-64:~$ cd tmp tiian@ubuntu1404-64:~/tmp$

Copy file example_xta_sa21.py in your working dir:

[Shell terminal session]
tiian@ubuntu:~/tmp$ cp /opt/lixa/share/doc/lixa-X.Y.Z/examples/xta/python/example_xta_sa21.py .

Substitute “X.Y.Z” with the actual version of the software you installed.

The example program accepts two arguments:

commit (or rollback): use “1” if you want to perform a global commit or “0” if you want to perform a global rollback
insert (or delete): use “1” if you want to insert rows in databases or “0” if you want to delete rows from databases

Open three terminal sessions: one for example execution,

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ ls -la total 12 drwxrwxr-x 2 tiian tiian 4096 ott 15 22:08 . drwxr-xr-x 16 tiian tiian 4096 ott 15 22:06 .. -rw-r--r-- 1 tiian tiian 3980 ott 15 22:08 example_xta_sa21.py

one for PostgreSQL queries

[Shell terminal session - PostgreSQL]
tiian@ubuntu1404-64:~$ psql testdb psql (9.3.24) Type "help" for help. testdb=>

and one for MySQL queries

[Shell terminal session - MySQL]

tiian@ubuntu1404-64:~$ mysql -h localhost -u lixa lixa
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 44
Server version: 5.5.61-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

set LIXA_PROFILE environment variable to XTA_DYN a profile without static Resource Managers defined in lixac_conf.xml:

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ export LIXA_PROFILE=XTA_DYN tiian@ubuntu1404-64:~/tmp$ echo $LIXA_PROFILE XTA_DYN

insert rows in tables and commit:

[Shell terminal session - AP]

tiian@ubuntu1404-64:~/tmp$ python example_xta_sa21.py 1 1
PostgreSQL, executing >INSERT INTO authors VALUES(1921, 'Rigoni Stern', 'Mario')<
MySQL, executing >INSERT INTO authors VALUES(1919, 'Levi', 'Primo')<
tiian@ubuntu1404-64:~/tmp$

check PostgreSQL table content:

[Shell terminal session - PostgreSQL]
testdb=> select * from authors; id \| last_name \| first_name ------+--------------+------------ 1921 \| Rigoni Stern \| Mario (1 row) testdb=>

check MySQL table content:

[Shell terminal session - MySQL]

mysql> select * from authors;
+------+-----------+------------+
| id   | last_name | first_name |
+------+-----------+------------+
| 1919 | Levi      | Primo      |
+------+-----------+------------+
1 row in set (0.00 sec)

mysql>

delete rows from tables, but rollback:

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ python example_xta_sa21.py 0 0 PostgreSQL, executing >DELETE FROM authors WHERE id=1921< MySQL, executing >DELETE FROM authors WHERE id=1919< tiian@ubuntu1404-64:~/tmp$

check PostgreSQL table content:

[Shell terminal session - PostgreSQL]
testdb=> select * from authors; id \| last_name \| first_name ------+--------------+------------ 1921 \| Rigoni Stern \| Mario (1 row) testdb=>

check MySQL table content:

[Shell terminal session - MySQL]

mysql> select * from authors;
+------+-----------+------------+
| id   | last_name | first_name |
+------+-----------+------------+
| 1919 | Levi      | Primo      |
+------+-----------+------------+
1 row in set (0.00 sec)

mysql>

delete rows and commit:

[Shell terminal session - AP]
tiian@ubuntu1404-64:~/tmp$ python example_xta_sa21.py 1 0 PostgreSQL, executing >DELETE FROM authors WHERE id=1921< MySQL, executing >DELETE FROM authors WHERE id=1919< tiian@ubuntu1404-64:~/tmp$

check PostgreSQL table content:

[Shell terminal session - PostgreSQL]
testdb=> select * from authors; id \| last_name \| first_name ----+-----------+------------ (0 rows) testdb=>

check MySQL table content:

[Shell terminal session - MySQL]
mysql> select * from authors; Empty set (0.00 sec) mysql>

Source Code

Source code for program example_xta_sa21.py is installed in directory /opt/lixa/share/doc/lixa-X.Y.Z/examples/xta/python and is available on GitHub. Source code is fully commented for readability.

^[47] Examples: mysql_real_connect for MySQL, PQconnectDB for PostgreSQL.

^[48] Some Resource Managers, like for example Oracle Database Server, don't allow to be created as “normal” connections and then transformed in XA connections: they must be directly created by XTA.