Re: Problem Locking in Exclusive Mode

In article <2ncnm5$p2c@emory.mathcs.emory.edu>, nnob@seq1.loc.gov (Nick Nobbe) says: > > Trying a rather massive delete (25 K rows) while in a transaction and > locking the table in exclusive mode, Informix reports something along > the lines of "Unable to update transaction log" and also "ISAM error". > > Selecting smaller sets for deletion worked, however. > > Also, with no problem whatsoever, setting locking to share mode. > > Using Informix SE 5.01 with SCO Unix 3.2.2. Is this a known bug? > No. It's a feature (*grin*). It's also a feature of several other database systems I've worked on that support transactions. Informix calls it a LONG TRANSACTION. In a generic sense, a system must always be able to reverse a transaction until it is committed. That means in practice, that the system must maintain copies of the data before and after modification. In Informix's implementation, the copy is kept in the logical logs. Any operation (INSERT, DELETE, UPDATE) whether in an explicit transaction (BEGIN WORK, COMMIT WORK), or in a single command must store a copy of the data in the logical logs. If the logical logs fill up, then the transaction must be reversed, since completion cannot be guaranteed until the operation is complete. You can get around this by: 1) Breaking your update into smaller sets using a 4GL or ESQL program, or even separate ISQL commands. 2) Configuring your system with more logical logs. Approach #1 is the most efficient and easier scalable to very large systems. If you make your logs too large, you stand the chance of a very long recovery in the event of a system stop, program error or user kill. Jim Burks jburks@promus.com Database Administrator The Promus Companies Memphis, TN 38125-8883