Re: ESQL/C - Critical Section?

Jack O'Hara wrote: > Madison, > > Thanks for the info on "critical section". > > However, is it then true to say that one transaction may have left critical > state from an update of one record, that record flushed to disk during a > checkpoint, but then perform another update of a different record (then say a > commit) which would be flushed to disk after the checkpoint completed? Yes - this will happen whenever a transaction spans a checkpoint. However, this will cause two critical sections, not one. > > > Also, based on an explanation of "long transactions" found in > comp.databases.informix archives at: > "http://www.iiug.org/members/cdi_archive/1999.04/cdii.32937";, > it attributes LARGE transactions and LONG transactions to having an impact > that would cause noticeably slow DB performance. > > Therefore, if the explanation of "long transactions" (period of time) is > accurate, and they do > not have a direct correlation to a transaction's critical section(s), then how > would > long transactions impact DB performance? I think there is some confussion in the memo. A long or large transaction does not impact DB performance. However, it can cause queing of transactions. If transaction 'A' has a lock on row-1 and transaction 'B' is trying to lock row-1, then transaction 'B' will queue on the row. That may give the 'appearance' that the DB is running slow. It is not. It's just that the second transaction is having to wait for the first transaction to complete. > > > Referring to my original ESQL/C coding question (below), if one transaction in a > single instance of a program happens to be coded with extended sleep periods in > between many updates/inserts of several different tables/rows, would this then > be considered a "long transaction"? > If so, would it have any direct effect on OVERALL DB performance however > negligible? How? See above ... Remember holding a locked resource is not the same as being in a critical section. > > > Thanks a lot, > Jack O'Hara > > Madison Pruet wrote: > > > When a transaction enters a critical section, it is doing somthing that if > > it fails, then the integrety of the engine is at risk. The most obvious > > thing would be somthing like when the log entry for an update is in the > > process of being put into the log buffer. Obviously the entire entry can't > > be put into the buffer as a single machine instruction. Also, we can't > > allow the checkpoint to occur while transactions are putting an entry into > > the log buffer since one of the things done during a checkpoint is to flush > > the log buffer. > > > > So, if a transaction is about to do somthing as sensitive as putting a entry > > into the log buffer, we mark the transaction as being in a critical state. > > When that activity is complete, we remove the critical state. > > > > The checkpoint waits until all transactions are out of a critical section. > > By the way, when the checkpoint begins, it causes a lock on any transcation > > going into a critical section. Obviously we don't want to hold the critical > > state for long. > > > > Jack O'Hara wrote: > > > > > Hi All, > > > > > > Running OLTP, Informix SQL Server 7.22, Solaris platform. > > > > > > Am trying to get a handle on understanding exactly when a transaction is > > > in a "critical section" and its effect on a checkpoint. So far, I > > > understand a checkpoint blocks any new transactions, but it must also > > > wait for all transactions that are in (or have reached??) a critical > > > section which results in longer checkpoint durations. > > > > > > When EXACTLY has a transaction entered/reached a "critical section"? > > > ...is it immediately after a "begin work"? > > > ...or after a "begin work" and some record has been > > > inserted/modified (created a dirty buffer page)? > > > ...or ??? > > > > > > Once the transaction is in a critical section, must the transaction > > > continue to the point of a commit/rollback before the checkpoint can > > > continue, or can the checkpoint continue at some earlier juncture (a > > > pause in transaction)? > > > > > > Applying answers for the above questions to an ESQL/C program then: > > > > > > If an ESQL/C program has done a "EXEC SQL BEGIN WORK" followed > > > immediately by "sleep(5)", is this transaction considered in a critical > > > section for the entire 5 second sleep causing the checkpoint to wait > > > during this time? > > > > > Nope. > > > > > > > > > > > If not, what if after the "EXEC SQL BEGIN WORK" an insert/update of a > > > record was performed before the "sleep(5)". Would this transaction be > > > considered in a critical section for the entire 5 second sleep causing > > > the checkpoint to wait during this time? > > > > Again nope. In both of these cases, the sleep(5) can only occur while > > control is in the esql program. Thus the sqlexec thread in the engine would > > be on a SMREAD condition or NETNORM. If it's there, it can't be in a > > critical section. > > > > > > > > > > > I've read past postings and Informix manuals on this topic, but still > > > find rather vague when attempting to apply to the above questions. > > > > > > Any enlightenment would be greatly appreciated. > > > > > > Thanks, > > > Jack O'Hara > > > johara@writeme.com