Nested Transaction Problem in MDM
Question: Can we achieve nested transaction in EJB 3 using a single connection, supporting different databases as Oracle, MySQL and SQLServer?
Use case:
1. Start a stateless session bean.
2. Get a connection
3. Update table A. (Transaction 1)
4. Update table B. (Transaction 2)
5. Commit the Transaction 2.
6. Based on some logic commit or rollback Transaction 1.
7. Close connection.
Problem: If we have XA mode, rolling back at step 6 will enforce Transaction 2 to roll back as well?
A solution using Oracle feature: PRAGMA AUTONOMOUS TRANSACTION in oracle actually can make a stored procedure or function independent transaction even if it is nested inside another transaction. For example the sequence ID generator in MDM.
CREATE OR
REPLACE FUNCTION SEQMGR (Seq_Name_In IN VARCHAR2, Chunk_Size_In IN INTEGER)
RETURN INTEGER
IS
PRAGMA AUTONOMOUS_TRANSACTION;Count_out INTEGER := 0;
BEGIN
Count_out := 0;
UPDATE SBYN_SEQ_TABLE SET seq_count = seq_count+Chunk_Size_In WHERE seq_name = Seq_Name_In;
SELECT seq_count-Chunk_Size_In INTO Count_out FROM SBYN_SEQ_TABLE WHERE seq_name = Seq_Name_In;
COMMIT;
RETURN Count_out;
EXCEPTION WHEN OTHERS THEN RETURN 0;
END SEQMGR;
Problem 2: MySQL does not support AUTONOMOUS. There could be other databases too which do not support this.
Possible Solution Exploration: java.sql.Savepoint Interface does not help. It would have helped if the outer transaction committed and the inner transaction rolled back. Use of two UserTransactions does not help if we have the same connection source if we use the XA mode.
