Controlling Transactions
SQLUnit can put each Connection object in one of three transaction modes. The mode is controlled by the transaction-support attribute. The table below summarizes the key characteristics of each mode.
Table 1. Transaction Mode characteristics
| Transaction-Support | AutoCommit Set? | COMMIT on success? | ROLLBACK on failure? |
|---|---|---|---|
| on (default) | No | Yes | Yes |
| off | No | No | No |
| implicit | Yes | No | No |
When transaction-support=on (the default), SQLUnit turns off JDBC AutoCommit mode and ensures that each SQL or CALL is committed on success and rolled back on failure. The unit of work it assumes is the block contained in either an sql.stmt or call.stmt tag. If a different unit of work is required for your tests, you should consider setting transaction-support=off.
When transaction-support=off, SQLUnit turns of JDBC AutoCommit mode. It will not do either COMMIT or ROLLBACK. The COMMIT or ROLLBACK has to be handled by the client by adding the appropriate statements in the stored procedures.
When transaction-support=implicit, SQLUnit will turn on JDBC AutoCommit mode, but not issue any COMMITs or ROLLBACKs. The job of controlling transactions is left to the database. Some databases, such as Sybase and MS SQL Server, have modes that intelligently handle transactional units of work on behalf of the client. In situations where the database does not offer these features, the JDBC transaction behavior will be used, where every SQL statement or stored procedure call will be treated as a single transaction.
 | Prior to version 4.6, the transaction support feature did not work correctly. AutoCommit was turned on in both transaction-support on and off modes. The only difference between the two modes was that SQLUnit did a COMMIT or ROLLBACK after executing a SQL or CALL statement in the "on" mode, and not in the "off" mode. The problem was not noticeable unless you explicitly wanted to change the default unit of work, and found that setting transaction-support=off did not produce the results you expected. The fix in version 4.6 was to set the default AutoCommit mode to false, so transaction-support=off would not do any COMMIT or ROLLBACK on its own. However, this created a problem for Sybase tests that use the CHAINED feature (and is likely to cause similar problems for MS SQL Server tests that use TRANSACTION MODE IMPLICIT, although none have been reported at the time of this writing) with transaction-support=off. Using CHAINED or IMPLICIT implies that the caller lets the database decide when to apply a COMMIT or ROLLBACK, if the database supports it, or apply COMMIT or ROLLBACK after every call (the JDBC default). This mode requires that AutoCommit must be turned on. For this scenario, a new transaction-support mode "implicit" has been introduced in version 4.7. If you experience problems with CHAINED mode or IMPLICIT mode in Sybase or MS SQL Server, then you should try to use the transaction-mode="implicit". |