TX support on SQL database

Nested TxScope
Common logic upon entering a TxScope based on
- TxType
- Isolation level
Common logic upon exiting a TxScope based on
- TxType
- Isolation level
- rollback status
SPI for DbService to integrate into Act's TX management framework
- beginTx()
- rollback()
- commit()
- cleanUp()

TxType	Logic
REQUIRED	create new TX if no existing found; use existing TX if found
REQUIRES_NEW	suspend existing TX if found; create new TX
MADATORY	raise exception if existing TX not found; use existing TX if found
SUPPORTS	use existing TX if found
NOT_SUPPORTED	suspend existing TX if found
NEVER	raise exception if existing TX found

Rollback	TxType	Logic
true	REQUIRED	if parent TX scope exists do nothing, otherwise rollback TX
true	REQUIRES_NEW	rollback TX and resume parent TX
true	MADATORY	do nothing
true	SUPPORTS	do nothing
true	NOT_SUPPORTED	N/A
true	NEVER	N/A
false	REQUIRED	if parent TX scope exists do nothing, otherwise commit TX
false	REQUIRES_NEW	commit TX and resume parent Tx
false	MADATORY	do nothing
false	SUPPORTS	do nothing
false	NOT_SUPPORTED	do nothing
false	NEVER	do nothing

beginTx
- get an new Connection
- Store previous isolation level
- Set required isolation level
- Store the connection with TX
rollback
- get the stored connection from TX object
- if there are savepoint attached with the TX object then
  - rollback the conn to the save point
- otherwise
  - rollback the conn
commit
- get the stored connection from TX object
- commit the conn
cleanUp
- get the stored connection from Tx object
- close the connection

Note It shall always get the connection from the current TxScope's TX object if possible for any database actvity

beginTx
- create an EntityManager em and associate it with the TX object
- call em.getTransaction().begin()
- get JDBC connection from em and stored it with the TX object
rollback
- get em from TX object
- call em.getTransaction().rollback()
- call em.clear()
commit
- get em from TX object
- call em.getTransaction().commit()
cleanUp
- get em from TX object
- clear transaction on em depending on implementation, e.g. Hibnerate needs to call transactionData.resetSessionState()
- close em

TODO:

Sunbelt Computer Software