Deadlock occurs during execution of converted code.

Symptom

Oracle function with DML was converted using SSMA. When the converted code is executed a deadlock may occur.

Cause

Extended procedure xp_ora2ms_exec2_ex is used for conversion of Oracle stored functions with DML and other statements prohibited in SQL Server UDFs. In this case, Oracle function is converted into implementation procedure (called $impl procedure) and this $impl procedure is called from generated UDF wrapper via xp_ora2ms_exec2_ex.

Extended procedure xp_ora2ms_exec2_ex, which is part of SSMA for Oracle Extension Pack, spawns a new connection to the same SQL Server and executes specified stored procedure in this connection. It is used in many Migration Issues throughout SSMA for Oracle. One of the parameters of this XP is bind_to_transaction which can be "true" or "false".

If bind_to_transaction is "true", xp_ora2ms_exec2_ex binds a new connection to external transaction. Otherwise, called stored procedure is executed in "automomous" transaction.

xp_ora2ms_exec2_ex cannot bind to external transaction if called from SQL Server UDF. That makes all calls to stored procedures made in spawned connection processed in "automomous" transaction, causing possible deadlocks and loss of transaction's atomicity.

Resolution

Replace function calls with direct EXECs of $impl procedures.

More Information

Article Id: KBO2SS1004

Applies to

SSMA 2005 for Oracle

SSMA 2008 for Oracle