Figure 5.7. Deploy model of an example showing a distributed transaction with MySQL, PostgreSQL and Oracle
This example shows as you can implement DTP (Distributed Transaction Processing) with three Resource Managers (MySQL, PostgreSQL and Oracle) 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 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.
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 Oracle, please refer to the official Oracle 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 Oracle technology: it is assumed you already installed and configured the database.
Important
The LIXA software must be configured to support the MySQL, PostgreSQL and Oracle resource managers as explained in the section called “Linking third party resource managers”. As a little hint, you should configure LIXA as below:
./configure --with-mysql \ > --with-postgresql-include=/usr/include/postgresql --with-postgresql-lib=/usr/lib \ > --with-oracle=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
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 “Oracle DMBS Configuration” to set-up a running environment for Oracle Database 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/example10_mys_pql_ora.c . tiian@ubuntu:~/tmp$ gcc example10_mys_pql_ora.c $(/opt/lixa/bin/lixa-config -c -f -m -p -d) \ > $(mysql_config --include --libs_r) -I/usr/include/postgresql -lpq \ > -I/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/rdbms/public \ > -L/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib -lclntsh -lnnz10 \ > -o example10_mys_pql_ora |
Verify the executable produced by gcc:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ldd example10_mys_pql_ora
linux-gate.so.1 => (0xb774b000)
liblixac.so.0 => /opt/lixa/lib/liblixac.so.0 (0xb7730000)
liblixapq.so.0 => /opt/lixa/lib/liblixapq.so.0 (0xb7729000)
liblixamy.so.0 => /opt/lixa/lib/liblixamy.so.0 (0xb7720000)
libmysqlclient_r.so.15 => /usr/lib/libmysqlclient_r.so.15 (0xb7530000)
libpq.so.5 => /usr/lib/libpq.so.5 (0xb7511000)
libclntsh.so.10.1 => not found
libnnz10.so => not found
libc.so.6 => /lib/tls/i686/cmov/libc.so.6 (0xb73c1000)
libgmodule-2.0.so.0 => /usr/lib/libgmodule-2.0.so.0 (0xb73bd000)
libdl.so.2 => /lib/tls/i686/cmov/libdl.so.2 (0xb73b9000)
libgthread-2.0.so.0 => /usr/lib/libgthread-2.0.so.0 (0xb73b4000)
librt.so.1 => /lib/tls/i686/cmov/librt.so.1 (0xb73ab000)
libglib-2.0.so.0 => /usr/lib/libglib-2.0.so.0 (0xb72fa000)
libxml2.so.2 => /usr/lib/libxml2.so.2 (0xb71d9000)
liblixab.so.0 => /opt/lixa/lib/liblixab.so.0 (0xb71c4000)
libm.so.6 => /lib/tls/i686/cmov/libm.so.6 (0xb719f000)
libpthread.so.0 => /lib/tls/i686/cmov/libpthread.so.0 (0xb7187000)
libcrypt.so.1 => /lib/tls/i686/cmov/libcrypt.so.1 (0xb7155000)
libnsl.so.1 => /lib/tls/i686/cmov/libnsl.so.1 (0xb713c000)
libz.so.1 => /usr/lib/libz.so.1 (0xb7127000)
libssl.so.0.9.8 => /usr/lib/i686/cmov/libssl.so.0.9.8 (0xb70e1000)
libcrypto.so.0.9.8 => /usr/lib/i686/cmov/libcrypto.so.0.9.8 (0xb6f9f000)
libkrb5.so.3 => /usr/lib/libkrb5.so.3 (0xb6f12000)
libcom_err.so.2 => /lib/libcom_err.so.2 (0xb6f0e000)
libgssapi_krb5.so.2 => /usr/lib/libgssapi_krb5.so.2 (0xb6ee5000)
libldap_r-2.4.so.2 => /usr/lib/libldap_r-2.4.so.2 (0xb6ea5000)
/lib/ld-linux.so.2 (0xb774c000)
libpcre.so.3 => /usr/lib/libpcre.so.3 (0xb6e7e000)
libuuid.so.1 => /lib/libuuid.so.1 (0xb6e7a000)
libk5crypto.so.3 => /usr/lib/libk5crypto.so.3 (0xb6e56000)
libkrb5support.so.0 => /usr/lib/libkrb5support.so.0 (0xb6e4e000)
libkeyutils.so.1 => /lib/libkeyutils.so.1 (0xb6e4b000)
libresolv.so.2 => /lib/tls/i686/cmov/libresolv.so.2 (0xb6e38000)
liblber-2.4.so.2 => /usr/lib/liblber-2.4.so.2 (0xb6e2b000)
libsasl2.so.2 => /usr/lib/libsasl2.so.2 (0xb6e13000)
libgnutls.so.13 => /usr/lib/libgnutls.so.13 (0xb6d9d000)
libtasn1.so.3 => /usr/lib/libtasn1.so.3 (0xb6d8d000)
libgcrypt.so.11 => /lib/libgcrypt.so.11 (0xb6d40000)
libgpg-error.so.0 => /lib/libgpg-error.so.0 (0xb6d3c000)
|
There are five unresolved references that can be fixed setting up the environment properly:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ echo $LD_LIBRARY_PATH tiian@ubuntu:~/tmp$ . /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh tiian@ubuntu:~/tmp$ echo $LD_LIBRARY_PATH /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib: |
Check again the executable:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ldd example10_mys_pql_ora linux-gate.so.1 => (0xb77c3000)
liblixac.so.0 => /opt/lixa/lib/liblixac.so.0 (0xb77a8000)
liblixapq.so.0 => /opt/lixa/lib/liblixapq.so.0 (0xb77a1000)
liblixamy.so.0 => /opt/lixa/lib/liblixamy.so.0 (0xb7798000)
libmysqlclient_r.so.15 => /usr/lib/libmysqlclient_r.so.15 (0xb75a8000)
libpq.so.5 => /usr/lib/libpq.so.5 (0xb7589000)
libclntsh.so.10.1 => /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libclntsh.so.10.1 (0xb67d5000)
libnnz10.so => /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libnnz10.so (0xb65d0000)
libc.so.6 => /lib/tls/i686/cmov/libc.so.6 (0xb6480000)
libgmodule-2.0.so.0 => /usr/lib/libgmodule-2.0.so.0 (0xb647c000)
libdl.so.2 => /lib/tls/i686/cmov/libdl.so.2 (0xb6478000)
libgthread-2.0.so.0 => /usr/lib/libgthread-2.0.so.0 (0xb6473000)
librt.so.1 => /lib/tls/i686/cmov/librt.so.1 (0xb646a000)
libglib-2.0.so.0 => /usr/lib/libglib-2.0.so.0 (0xb63b9000)
libxml2.so.2 => /usr/lib/libxml2.so.2 (0xb6298000)
liblixab.so.0 => /opt/lixa/lib/liblixab.so.0 (0xb6283000)
libm.so.6 => /lib/tls/i686/cmov/libm.so.6 (0xb625e000)
libpthread.so.0 => /lib/tls/i686/cmov/libpthread.so.0 (0xb6246000)
libcrypt.so.1 => /lib/tls/i686/cmov/libcrypt.so.1 (0xb6214000)
libnsl.so.1 => /lib/tls/i686/cmov/libnsl.so.1 (0xb61fb000)
libz.so.1 => /usr/lib/libz.so.1 (0xb61e6000)
libssl.so.0.9.8 => /usr/lib/i686/cmov/libssl.so.0.9.8 (0xb61a0000)
libcrypto.so.0.9.8 => /usr/lib/i686/cmov/libcrypto.so.0.9.8 (0xb605e000)
libkrb5.so.3 => /usr/lib/libkrb5.so.3 (0xb5fd1000)
libcom_err.so.2 => /lib/libcom_err.so.2 (0xb5fcd000)
libgssapi_krb5.so.2 => /usr/lib/libgssapi_krb5.so.2 (0xb5fa4000)
libldap_r-2.4.so.2 => /usr/lib/libldap_r-2.4.so.2 (0xb5f64000)
/lib/ld-linux.so.2 (0xb77c4000)
libpcre.so.3 => /usr/lib/libpcre.so.3 (0xb5f3d000)
libuuid.so.1 => /lib/libuuid.so.1 (0xb5f39000)
libk5crypto.so.3 => /usr/lib/libk5crypto.so.3 (0xb5f15000)
libkrb5support.so.0 => /usr/lib/libkrb5support.so.0 (0xb5f0d000)
libkeyutils.so.1 => /lib/libkeyutils.so.1 (0xb5f0a000)
libresolv.so.2 => /lib/tls/i686/cmov/libresolv.so.2 (0xb5ef7000)
liblber-2.4.so.2 => /usr/lib/liblber-2.4.so.2 (0xb5eea000)
libsasl2.so.2 => /usr/lib/libsasl2.so.2 (0xb5ed2000)
libgnutls.so.13 => /usr/lib/libgnutls.so.13 (0xb5e5c000)
libtasn1.so.3 => /usr/lib/libtasn1.so.3 (0xb5e4c000)
libgcrypt.so.11 => /lib/libgcrypt.so.11 (0xb5dff000)
libgpg-error.so.0 => /lib/libgpg-error.so.0 (0xb5dfb000)
|
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_ORA_DYN tiian@ubuntu:~/tmp$ echo $LIXA_PROFILE MYS_STA_PQL_STA_ORA_DYN |
It is suggested to set the necessary environment variables in your
profile if you are going to execute the programs many times.
This is the list of the suggested variables:
LD_LIBRARY_PATH,
LIXA_PROFILE,
ORACLE_HOME,
ORACLE_SID,
PATH.
We set LIXA_PROFILE to value
“MYS_STA_PQL_STA_ORA_DYN”, looking at
/opt/lixa/etc/lixac_conf.xml:
<profile name="MYS_STA_PQL_STA_ORA_DYN">
<sttsrvs>
<sttsrv>local_1</sttsrv>
</sttsrvs>
<rsrmgrs>
<rsrmgr>MySQL_stareg</rsrmgr>
<rsrmgr>PostgreSQL_stareg</rsrmgr>
<rsrmgr>OracleXE_dynreg</rsrmgr>
</rsrmgrs>
</profile>
the profile references three Resource Managers: “MySQL_stareg”, “PostgreSQL_stareg” and “OracleXE_dynreg”, 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="" />
<rsrmgr name="OracleXE_dynreg" switch_file="/opt/lixa/lib/switch_oracle_dynreg.so" xa_open_info="Oracle_XA+Acc=P/hr/hr+SesTm=30+LogDir=/tmp+threads=true+DbgFl=7+Loose_Coupling=true" xa_close_info="" />
we can discover how our application will access the resource managers [34] [35] [36].
Verify no (Oracle) trace file exists:
tiian@ubuntu:~/tmp$ ls -la /tmp/xa* ls: cannot access /tmp/xa*: No such file or directory
It is suggested to open four different terminals: the first one connected to “lixa” MySQL database, the second one connected to “testdb” PostgreSQL database, the third one connected to Oracle database and the fourth one pointing to the directory where the compiled program example10_mys_pql_ora 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=>
|
| [Oracle terminal session] |
tiian@ubuntu:~$ . /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh tiian@ubuntu:~$ sqlplus "hr/hr" SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 3 21:52:06 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production SQL> |
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ls -la total 152 drwxr-xr-x 2 tiian tiian 4096 2011-11-11 21:06 . drwxr-xr-x 40 tiian tiian 4096 2011-11-11 15:47 .. -rwxr-xr-x 1 tiian tiian 12317 2011-11-11 21:06 example10_mys_pql_ora -rw-r--r-- 1 tiian tiian 8422 2011-11-11 21:05 example10_mys_pql_ora.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) |
Check the content of the Oracle table (“COUNTRIES”):
| [Oracle terminal session] |
SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; no rows selected |
Insert a row in all the tables and check the contents of the tables after the transaction execution:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ./example10_mys_pql_ora insert Inserting a row in MySQL table... Inserting a row in PostgreSQL table... Inserting a row in Oracle 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) |
| [Oracle terminal session] |
SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- RS Repubblica San Marino 1 |
With the opposite command you can remove the rows from the tables:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ./example10_mys_pql_ora delete Deleting a row from MySQL table... Deleting a row from PostgreSQL table... Deleting a row from Oracle 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) |
| [Oracle terminal session] |
SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; no rows selected |
We can verify the dynamic registration behavior of Oracle:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ export LIXA_TRACE_MASK=0x00002000 tiian@ubuntu:~/tmp$ echo $LIXA_TRACE_MASK 0x00002000 tiian@ubuntu:~/tmp$ ./example10_mys_pql_ora insert 2>&1 | grep ax_reg 2011-11-11 21:39:40.850356 [23176/3051161344] ax_reg: rmid=2, xid=0xbfecc68c, flags=0x0 2011-11-11 21:39:40.850451 [23176/3051161344] ax_reg: the application program has started a transaction (TX states S3); this XID '1279875137.0600c467d21b42b38f1c89d912fc125d.8be5bdb35ba638bb997258e6bd8b9d88' will be returned 2011-11-11 21:39:40.850565 [23176/3051161344] ax_reg: sending 153 bytes to the server for step 8 2011-11-11 21:39:40.850602 [23176/3051161344] ax_reg/excp=7/ret_cod=0/errno=0 |
We can check the static registration behavior of Oracle with a different profile:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ export LIXA_PROFILE=MYS_STA_PQL_STA_ORA_STA tiian@ubuntu:~/tmp$ echo $LIXA_PROFILE MYS_STA_PQL_STA_ORA_STA tiian@ubuntu:~/tmp$ ./example10_mys_pql_ora delete 2>&1 | grep xa_start 2011-11-11 21:41:52.953050 [23204/3050981120] lixa_xa_start [...] 2011-11-11 21:41:53.005001 [23204/3050981120] lixa_xa_start: xa_start_entry(xid, 0, 0x0) = 0 2011-11-11 21:41:53.007040 [23204/3050981120] lixa_xa_start: xa_start_entry(xid, 1, 0x0) = 0 2011-11-11 21:41:53.009927 [23204/3050981120] lixa_xa_start: xa_start_entry(xid, 2, 0x0) = 0 2011-11-11 21:41:53.010004 [23204/3050981120] lixa_xa_start: sending 352 bytes to the server for step 24 2011-11-11 21:41:53.010095 [23204/3050981120] lixa_xa_start/excp=10/ret_cod=0/errno=0 |
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) |
| [Oracle terminal session] |
SQL> select * from COUNTRIES where COUNTRY_ID = 'RS'; no rows selected |
The activity of the Oracle database can be analyzed in the trace file:
| [Shell terminal session] |
tiian@ubuntu:~/tmp$ ls -la /tmp/xa_NULL11112011.trc -rw-r--r-- 1 tiian tiian 22112 2011-11-11 21:47 /tmp/xa_NULL11112011.trc |
[34]
The content of xa_open_info for MySQL is
described in
the section called “Some checks before program execution”.
[35]
The content of xa_open_info is passed to
PQconnectdb function: you can refer to
PostgreSQL official
documentation to discover what you can pass to this function.
Please pay attention the xa_open_info can
contain a maximum of 255 characters: this limitation is
documented in [XAspec].
[36]
The content of xa_open_info string is
described in chapter 15 "Developing Applications with Oracle XA"
of the "Oracle Database Application Developer's Guide"
manual; please refer to the documentation published by
Oracle Corporation for further details.