Our .net 6 application will need to insert nearly 10 tables as part of a single transaction. The last table contains an identity primary key column which is serial type. From a collection of data, each data needs to be inserted into 10 tables. Within that transaction we have small calculations, using the primary key generated by table insertion in following table insertion.
Everything works fine until the transaction is done sequentially in normal foreach loop. After introducing the Parallel.ForEachAsync , few data got processed successfully and few got failed because of the exception thrown from last table insertion.
"[Informix][Informix ODBC Driver][Informix]Could not insert new row into the table. sqlerrm(TABLE_NAME)"
Technology stack:
From the documentation, we can observe that there is a lock in the table which gave exception to another thread attempting to insert into same table. What are the solutions to insert this in multi-threaded program (in this case Parallel.ForEachAsync method) ?
Can we use any isolation level which avoid this issue? I tried ReadUnCommited, ReadCOmmited, Serializable. I don't know how to proceed in this way further
ODBC provider doesn't provide any API to control the informix locks in the .net 6 code.
We couldn't alter the DDL or any other Informix database infrastructure or any settings there.
When there is a lock, the program or thread will get error or Exception from informix by default. Alternatively we can modify the behavior to wait until lock got released.
SET LOCK MODE TO WAIT
Using the above Set statement in my code as below fixes my problem. Now the insert query will wait and insert once lock released by another thread in my case.
var cmd = conn.CreateCommand();
cmd.CommandText = "SET LOCK MODE TO WAIT; INSERT INTO TABLE_NAME(column1, column2) VALUES(1, "value");";
var rowsAdded = command.ExecuteNonQuery();
To not to make any undesired impact unknowingly in other parts of our program(since it is vast and complicated code base), I've set the lock mode back to "No wait" i.e. informix will throw error like below.
cmd.CommandText = "SET LOCK MODE TO WAIT; INSERT INTO TABLE_NAME(column1, column2) VALUES(1, "value"); SET LOCK MODE TO NO WAIT;";