While trying to run our application with Informix IDS server I had to solve two problems:
- Cannot delete many rows from single table because of long running transaction error.
- Receive locking error during parallel batch processing on distinct data (ie. one table, distinct rows).
So I’ve read some manuals about Informix: Administrator’s Guide, Database Design and Implementation Guide, Guide to SQL – Syntax, Installation Guide for Microsoft Windows, JDBC Driver Programmer’s Guide and Informix Unleashed book. It took me 4 days to understand Informix specific issues The database is quite complex and has many interesting features (eg. table inheritance, vritual processors, …).
The result is, that there is no good solution
The “solution” is following:
- Drop all indexes, primary and foreing keys on such table, change table type to raw (without transaction log). Delete data. Recreate indexes, primary and foreing keys. (or delete data by chunks, use XPS server and operational table type). That’s quite clumsy isn’t it?
- Change default locking granurality for all table to row mode (default is page mode) and set sime timeout for waiting for exclusive lock (default is no wait). That doesn’t help to get rid of all locking errors, becaus Informix locks also previous and following record in all indexes over table…
I wrote a simple test to test locking problem on other databases we use (DB2 AS400, DB2 9.1, PostgreSQL, Oracle 10g, Firebird and MS SQL Server 2005).
The test simmulates parallel batch processing:
- create connection 1 (session – we use Hibernate)
- start transaction1
- update some rows in tableA
- create connection2
- start transaction2
- update some other rows in tableA
- commit transaction2
- commit transaction1
The result’s are:
- Informix IDS – fails with default settings, second update tries to lock index records that are already locked by first update … this is more or less probable. Wait with my settings until transaction1 commits.
- MS SQL Server 2005 – Wait until transaction1 commits, second update tries to lock page that is already locked by first update, which causes waiting. The documentation says that server chooses appropriate lock (db, table, page, row) but as I could see it does not. You can force MS SQL Server 2005 to use row lock. But only at pre statement basis and that’s not comfortable.
- DB2 AS400, DB2 9.1, PostgreSQL, Oracle 10g and Firebird passed my test without waiting.
Now I’m little frustrated about what database to use for combined OLTP and batch processing thas are required by our application.