A database transaction in a simple system is nothing complicated. But if you have distributed system there might be pretty serious problems. I want to explain it in a simple example of booking a hotel and fly ticket.
There are 2 transactions:
1)booking a hotel;
2)booking a fly ticket.
The logic is simple: if there is a free hotel room and free seat at plane - both transactions should be successfull.
But what if there is no free room, but the fly booking is possible? Second transaction is OK, but you don't need it anymore. So you have to cancel it manually. And, by the way, you have to control all this process all the time. In a large distributed process with thousands of transactions it will be a pain in the ass :)
ACT II : Preparing is our pill
With a 2PC there is a possibility to "predict" transaction's outcome. And, if everything is very bad
there is a rollback mechanism to return the system into its initial state.
 |
| This diagrams clearly shows 2PC process |
To make it possible I used Java (for making Transaction Manager) and pgAdmin III (for creating postgreSQL databases).
Links:
4. Eclipse
While installing PostgreSQL you will create a role for connecting, for me it is
name: postgres
password: admin
That's all for Postgres, all the other work will be done by code. For working with SQL the first line of my code is
import java.sql.*;
While installing PostgreSQL you will create a role for connecting, for me it is
name: postgres
password: admin
 |
| Having been connected to PostgreSQL server, I created 2 databases: FlyBooking and HotelBooking |
import java.sql.*;
1. Testing JDBC driver:
testingJDBC code
2. Creating tablescreatingTables:
Connection connection = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/FlyBooking", "postgres", "admin");
//This line stands for setting connection to FlyBookind database with postges role credentials.
All the procedures with SQL must be in try-catch block, otherwise the code won't be compiled.
3. For working with 2PC protocol in Java there is XA transaction class
documentation
import javax.transaction.xa.*;
import org.postgresql.xa.*;
import org.postgresql.core.*;
//This libraries are required to make all possible
Code that makes realisation of everything we need to communicate with database in terms of 2PC.
4. The last step is creating XA connection to database, making insert transaction and preparing it. If the transaction failed to be prepared, than it will be rolled back.
Full code for realisation of this work.
5. In order to test it, I commented commit lines
//xconn.commit(a, true);
//xconn2.commit(b, true);
and ran the application. The result was shown by the next queary to Postgres:
select * from pg_prepared_xacts;
 |
| As a result I had prepared transaction in temporary table |
