Distributed locks are created in Oracle by transaction involving a two phase commit or XA transaction or also called Distributed Transaction. It is important to note that it’s NOT necessary to have 2 databases to have a distributed transaction. For e.g. from a Weblogic Server, the datasource can be configured as an XA datasource if the transaction involves JMS as well as database. Hence the transaction in database will be classified as distributed transaction. Sometimes, these transactions can create locks in the database which are called the distributed locks. These locks can withstand a restart of the database. The effect of the distributed lock would be below error in some other part of the application which is trying to access the locked data.
ORA-01591: lock held by in-doubt distributed transaction 1.2.456789
The above error means that there is a distributed lock present in the database, which is locking the data requested by the end user. This means that a transaction has progressed to some state in this database and progressed to a stage in the second source which is part of the distributed transaction. Oracle, in most case, would be able to resolve the condition by the background processes. In case Oracle is not able to resolve the condition, it results in a distributed lock, which needs to be manually resolved. Unless the transaction is manually committed or rolled back, Oracle will not allow any other transactions, including read to happen on the data held by the lock. Oracle prevents read also as it doesn’t know as to which version of data should be shown to the end user.
By querying the pending_trans$ table (under sys schema), the details of the distributed lock can be obtained. The Local Tran Id present in the table should be used to force a commit or rollback of the transaction. The data in the pending_trans$ table should NOT be deleted to release the locks as it will not release the locks. Pending_trans$ table is only a means to show the DBA about the transactions which are to be manually cleared. The syntax for a rollback or commit is shown below
Sql> Commit force 1.2.456789;
Or
Sql> Rollback force 1.2.456789;
Once the data is committed or rolled back, the lock would be cleared and data can be accessed. The DBA should decide if the transaction should be committed or rolled back based on the data in the participating datasource. Also the columns in the pending_trans$ table can help decide the type of command to be used (will cover the details in the next blog. Place a comment if you need this info and I would be more than happy to add it on priority).
In case, the data is deleted from the pending_trans$ table, some extra steps are required to clear the locks. Again I will cover this in the next blog.