DB record are locked problem (informix DB)

, ,
1

Hi All,

I am facing a problem in record lock. below is the complete description of the problem please help.

Interface flow:
Source
I have a flowservice which connects to informix db and picks some records from it and updates the process flag as the processing takes place.
so during this time it publishes(local publish no broker involved) the document and updates the process flag in informix db.

Target
The published document is subscribed by the Target and inserts it in oracle db.and publishes a ACknowledgement document which contains invoice numbers in it.

Source
This ACknowledgement document is subscribed by Source and deletes the records from informix db based on the invoice no present in the ACknowledgement document published by target.

Triggers are of : concurrent type
jdbc connector : tried both No_transaction & local-transaction

Problem Faced:

getting the following error for few of the transactions and not for all.

:-1: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service
Invoice.Source.JDBCServices:deleteInvoice.
[ADA.1.316] Cannot execute the SQL statement “delete temp_esn where trim(invoice_number) = ?”. "
(IX000/-244) Could not do a physical-order read to fetch next row.
(IX000/-107) ISAM error: record is locked."
Could not do a physical-order read to fetch next row.

please suggest me how to stop this error.

thanks & regards
Sree

2

Hi
This may be caused because your webMethods transaction or flow is retrying to do some activity on the Informix table with out closing the table connection
Contact your informix admin to remove the webMethods connection or lock from the table and re run your service.I think it should be fine

thanks
Anil

3

If I recall correctly we faced similar issue with Informix database.

Record lock issue is because of the transaction isolation level for the database. You can manually set the isolation level using Other Properties Field of the JDBC connection

If you do not specify the isolation level in the Other Properties field, the default isolation level of the database is considered. If you specify an isolation level that is not supported by the database, an error is thrown while enabling the connection

IDS isolation levels and their ANSI counterparts
Informix SQL - ANSI SQL
Dirty read - Read uncommittted
Committed read -Read committed
Cursor stability -Not available
Repeatable read - Serializable

Transaction isolation levels for each.

Transaction Isolation Settings Value
TRANSACTION_READ_UNCOMMITTED 1
TRANSACTION_READ_COMMITTED 2
TRANSACTION_REPEATABLE_READ 4
TRANSACTION_SERIALIZABLE 8

Jiten

4

Hi Jiten / Anil,

Thanks for your reply.

Jiten,

Currently I have set the Isolation as
"Set Isolation to Dirty Read"
and the lock mode is
Row wise Locking

But still I am seeing this error. Can you please let me know what properties you have set it so this error got resolved.

regards
Sreenath.

5

Do we always do a record level locking or can we do a table level locking also with webMethods adapter services?