JDBC 1 DataSource versus XADataSource
When do I need an XADataSource?
You need an XADataSource if you want to execute a Distributed Transaction. You need a Distributed Transaction if you have a transaction with 2 or more different (not equal) DataSources.
Advantages of using an XADataSource
Disadvantages of using an XADataSource
An XADataSource always involves a performance overhead.
The risk of a deadlock is higher. Particularly if you use an emulation like the XDataSource.
You need more physical connections if you use an emulation XADataSource like XDataSource.
Which XADataSource should I use with the MS SQL Server?
The best solution is to use the DTCDataSource with the SQL Server 2000. The DTCDataSource works in conjunction with the MSDTC. You need to install the Resource Manager Proxy once per SQL Server instance. The SQL Server 7.0 does not implement the full XA standard. Problems can occur with some Application servers in certain programming situations.
The next solution is to use the XDataSource with access to the master database. This works with all SQL Server versions but it is only a simulation. Real recover is not supported. You should have access to the master database (tables sysglobaltransactions and sysglobaltransactionsold).
Another solution is to use the XDataSource without access to the master database (not preferred, though!). In this case you can have deadlocks if you use 2 connections on the same database objects in one transaction. In most of these cases you can use a JDBC 1 DataSource.
Which JDBC API can I use with a JDBC 1 DataSource?
You can use all the features of your driver. If you have a JDBC 2.0 driver then you can use the JDBC 2.0 API without Distributed Transactions. That is to say the connection was created with the DriverManager. This is a part of the JDBC 1 API. Therefore, you can also use a JDBC 1 driver with this configuration.