This example shows as you can implement DTP (Distributed Transaction Processing) with two Resource Managers (MySQL and PostgreSQL) coordinated by the LIXA Transaction Manager. It's strongly suggested you have played with the examples previously shown in this chapter (see Chapter 5, Developing C Application Programs using TX (Transaction Demarcation) interface) before starting this more complex one.
Note
If you did not yet installed the software provided by PostgreSQL, please refer to the official PostgreSQL site to download the software and to pick-up the information necessary to install and configure the database. This manual does not give you information related to PostgreSQL technology: it is assumed you already installed and configured the database.
If you did not yet installed the software provided by MySQL, please refer to the official MySQL site to download the software and to pick-up the information necessary to install and configure the database. This manual does not give you information related to MySQL technology: it is assumed you already installed and configured the database.
Important
The LIXA software must be configured to support the MySQL and PostgreSQL and resource managers as explained in the section called “Linking third party resource managers”. As a little hint, you should configure LIXA as below (Ubuntu):
./configure --with-mysql \ > --with-postgresql-include=/usr/include/postgresql --with-postgresql-lib=/usr/lib
or as below (Centos):
./configure --with-mysql \ > --with-postgresql-include=/usr/include/postgresql --with-postgresql-lib=/usr/lib
Please don't forget you must compile and install every time you re-configure.
Please follow the instructions explained
in the section called “MySQL/MariaDB 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
Prepare the client (Application Program) using the below commands (gcc command was splitted on several lines using \ to help readability, but you may use a single line):
| [Shell terminal session] |
tiian@ubuntu:~$ mkdir tmp tiian@ubuntu:~$ cd tmp tiian@ubuntu:~/tmp$ cp /opt/lixa/share/doc/lixa-X.Y.Z/examples/example9_mys_pql.c . tiian@ubuntu:~/tmp$ gcc example9_mys_pql.c $(/opt/lixa/bin/lixa-config -c -f -m -p -d) \ > $(mysql_config --include --libs_r) -I/usr/include/postgresql -lpq \ > -o example9_mys_pql |
Verify the executable produced by gcc:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ldd example9_mys_pql
linux-gate.so.1 => (0xb76f6000)
liblixac.so.0 => /opt/lixa/lib/liblixac.so.0 (0xb76db000)
liblixamy.so.0 => /opt/lixa/lib/liblixamy.so.0 (0xb76d3000)
liblixapq.so.0 => /opt/lixa/lib/liblixapq.so.0 (0xb76cb000)
libmysqlclient_r.so.15 => /usr/lib/libmysqlclient_r.so.15 (0xb74db000)
libpq.so.5 => /usr/lib/libpq.so.5 (0xb74bc000)
libc.so.6 => /lib/tls/i686/cmov/libc.so.6 (0xb736d000)
libgmodule-2.0.so.0 => /usr/lib/libgmodule-2.0.so.0 (0xb7369000)
libdl.so.2 => /lib/tls/i686/cmov/libdl.so.2 (0xb7365000)
libgthread-2.0.so.0 => /usr/lib/libgthread-2.0.so.0 (0xb735f000)
librt.so.1 => /lib/tls/i686/cmov/librt.so.1 (0xb7356000)
libglib-2.0.so.0 => /usr/lib/libglib-2.0.so.0 (0xb72a5000)
libxml2.so.2 => /usr/lib/libxml2.so.2 (0xb7185000)
liblixab.so.0 => /opt/lixa/lib/liblixab.so.0 (0xb7170000)
libpthread.so.0 => /lib/tls/i686/cmov/libpthread.so.0 (0xb7157000)
libcrypt.so.1 => /lib/tls/i686/cmov/libcrypt.so.1 (0xb7125000)
libnsl.so.1 => /lib/tls/i686/cmov/libnsl.so.1 (0xb710d000)
libm.so.6 => /lib/tls/i686/cmov/libm.so.6 (0xb70e8000)
libz.so.1 => /usr/lib/libz.so.1 (0xb70d3000)
libssl.so.0.9.8 => /usr/lib/i686/cmov/libssl.so.0.9.8 (0xb708e000)
libcrypto.so.0.9.8 => /usr/lib/i686/cmov/libcrypto.so.0.9.8 (0xb6f4b000)
libkrb5.so.3 => /usr/lib/libkrb5.so.3 (0xb6ebe000)
libcom_err.so.2 => /lib/libcom_err.so.2 (0xb6ebb000)
libgssapi_krb5.so.2 => /usr/lib/libgssapi_krb5.so.2 (0xb6e92000)
libldap_r-2.4.so.2 => /usr/lib/libldap_r-2.4.so.2 (0xb6e52000)
/lib/ld-linux.so.2 (0xb76f7000)
libpcre.so.3 => /usr/lib/libpcre.so.3 (0xb6e2a000)
libuuid.so.1 => /lib/libuuid.so.1 (0xb6e26000)
libk5crypto.so.3 => /usr/lib/libk5crypto.so.3 (0xb6e03000)
libkrb5support.so.0 => /usr/lib/libkrb5support.so.0 (0xb6dfb000)
libkeyutils.so.1 => /lib/libkeyutils.so.1 (0xb6df8000)
libresolv.so.2 => /lib/tls/i686/cmov/libresolv.so.2 (0xb6de4000)
liblber-2.4.so.2 => /usr/lib/liblber-2.4.so.2 (0xb6dd7000)
libsasl2.so.2 => /usr/lib/libsasl2.so.2 (0xb6dc0000)
libgnutls.so.13 => /usr/lib/libgnutls.so.13 (0xb6d4a000)
libtasn1.so.3 => /usr/lib/libtasn1.so.3 (0xb6d3a000)
libgcrypt.so.11 => /lib/libgcrypt.so.11 (0xb6cec000)
libgpg-error.so.0 => /lib/libgpg-error.so.0 (0xb6ce8000)
|
Set-up the necessary environment variables:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ echo $LIXA_PROFILE tiian@ubuntu:~/tmp$ export LIXA_PROFILE=MYS_STA_PQL_STA tiian@ubuntu:~/tmp$ echo $LIXA_PROFILE MYS_STA_PQL_STA |
It is suggested to set the necessary environment variable
(LIXA_PROFILE) in your
profile if you are going to execute the programs many times.
We set LIXA_PROFILE to value
“MYS_STA_PQL_STA”, looking at
/opt/lixa/etc/lixac_conf.xml:
<profile name="MYS_STA_PQL_STA">
<sttsrvs>
<sttsrv>local_1</sttsrv>
</sttsrvs>
<rsrmgrs>
<rsrmgr>MySQL_stareg</rsrmgr>
<rsrmgr>PostgreSQL_stareg</rsrmgr>
</rsrmgrs>
</profile>
the profile references two Resource Managers: “MySQL_stareg” and “PostgreSQL_stareg”, looking again at the config file:
<rsrmgr name="MySQL_stareg" switch_file="/opt/lixa/lib/switch_mysql_stareg.so" xa_open_info="host=localhost,user=lixa,passwd=,db=lixa,client_flag=0" xa_close_info="" />
<rsrmgr name="PostgreSQL_stareg" switch_file="/opt/lixa/lib/switch_postgresql_stareg.so" xa_open_info="dbname=testdb" xa_close_info="" />
we can discover how our application will access the resource managers [32] [33].
It is suggested to open three different terminals: the first one connected to “lixa” MySQL database, the second one connected to “testdb” PostgreSQL database and the third one pointing to the directory where the compiled program example9_mys_pql lives.
| [MySQL terminal session] |
tiian@ubuntu:~$ 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 37 Server version: 5.0.51a-3ubuntu5.8 (Ubuntu) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> |
| [PostgreSQL terminal session] |
tiian@ubuntu:~$ psql testdb
Welcome to psql 8.3.15, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
testdb=>
|
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ls -la total 28 drwxr-xr-x 2 tiian tiian 4096 2011-09-20 21:53 . drwxr-xr-x 40 tiian tiian 4096 2011-09-19 20:54 .. -rwxr-xr-x 1 tiian tiian 10096 2011-11-03 20:45 example9_mys_pql -rw-r--r-- 1 tiian tiian 4602 2011-11-03 20:45 example9_mys_pql.c |
Check the content of the MySQL table (“authors”):
| [MySQL terminal session] |
mysql> SELECT * FROM authors; Empty set (0.02 sec) |
Check the content of the PostgreSQL table (“AUTHORS”):
| [PostgreSQL terminal session] |
testdb=> select * from AUTHORS; id | last_name | first_name ----+-----------+------------ (0 rows) |
Insert a row in all the tables and check the contents of the tables after the transaction execution:
| [Third terminal session] |
tiian@ubuntu:~/tmp$ ./example9_mys_pql insert Inserting a row in MySQL table... Inserting a row in PostgreSQL table... |
Now you can verify the content of the tables after the transaction:
| [MySQL terminal session] |
mysql> SELECT * FROM authors; +----+-----------+------------+ | id | last_name | first_name | +----+-----------+------------+ | 1 | Foo | Bar | +----+-----------+------------+ 1 row in set (0.00 sec) |
| [PostgreSQL terminal session] |
testdb=> select * from AUTHORS; id | last_name | first_name ----+-----------+------------ 1 | Foo | Bar (1 row) |
With the opposite command you can remove the rows from the tables:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ./example9_mys_pql delete Deleting a row from MySQL table... Deleting a row from PostgreSQL table... |
and check the content of the tables again:
| [MySQL terminal session] |
mysql> SELECT * FROM authors; Empty set (0.00 sec) |
| [PostgreSQL terminal session] |
testdb=> select * from AUTHORS; id | last_name | first_name ----+-----------+------------ (0 rows) |
[32]
The content of xa_open_info for MySQL is
described in
the section called “Some checks before program execution”.