Long transaction
Hi, when long transaction being rollback, could others execute SQL select that don't write logical log? thanks.
Yes. Even other transactions are not blocked until the high water marks are reached. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Nov 18, 2011 at 12:38 AM, CHUAN LU <luchuan@cn.ibm.com> wrote: > Hi, > > when long transaction being rollback, could others execute SQL select that > don't write logical log? > > thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf301d417494278b04b20013aa
The answer is YES for SELECTS. For UPDATES, DELETES, INSERTS, the other sessions can still do work and write into the logs if the LTXHWM is reached for the long transaction being rolledback and the LTWEHWM has NOT been reached yet; since rolling back a transaction consists of a series of CLR records written to the transaction log that perform a bunch of undo operations until the BEGIN of the transaction is reached. When the LTXEHWM is reached and the long transaction has not been terminated, all of the sessions will be blocked and the engine will take the rest of the logs free space between LTXEHWM and the end of the logs free space to rollback the long transaction in question. Be careful, depeding on the version of the engine you are using, you can hung you engine if DYNAMIC_LOGS is not set in the ONCONFIG. Only IBM support can help in this case (a restore wihtout logs can also do but you loose all of the transactions since the latest backup). For previous versions not allowing DYNAMIC_LOGS , LTXHWM and LTXEHWM were set by default to 50 and 60% respectively. For the latest version allowing dynamic logs (if set), LTXHWM and LTXEHWM are set to 70 and 80% respectively. Cordialement, Regards, Khaled Bentebal Directeur Général - ConsultiX Président UGIF - User Group Informix France IIUG - Board of Directors Tél: 33 (0) 1 39 12 18 00 Fax: 33 (0) 1 39 12 18 18 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr Le 18/11/11 06:38, CHUAN LU a écrit : > Hi, > > when long transaction being rollback, could others execute SQL select that > don't write logical log? > > thanks. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
One side note: In most systems, the size of the logical logs used means that 70% of it is really huge. We all should consider if we really want to allow a transaction that spans across 70% of the logical log space defined... Also note, that on current versions these parameters can be changed dinamically... which means you can have them relatively low and increase them before any esporadic maintenance that requires a bigger value. Regards. On Fri, Nov 18, 2011 at 11:07 AM, Khaled Bentebal < khaled.bentebal@consult-ix.fr> wrote: > The answer is YES for SELECTS. > > For UPDATES, DELETES, INSERTS, the other sessions can still do work and > write into the logs if the LTXHWM is reached for the long transaction > being rolledback and the LTWEHWM has NOT been reached yet; since rolling > back a transaction consists of a series of CLR records written to the > transaction log that perform a bunch of undo operations until the BEGIN > of the transaction is reached. When the LTXEHWM is reached and the long > transaction has not been terminated, all of the sessions will be blocked > and the engine will take the rest of the logs free space between LTXEHWM > and the end of the logs free space to rollback the long transaction in > question. > > Be careful, depeding on the version of the engine you are using, you can > hung you engine if DYNAMIC_LOGS is not set in the ONCONFIG. Only IBM > support can help in this case (a restore wihtout logs can also do but > you loose all of the transactions since the latest backup). > For previous versions not allowing DYNAMIC_LOGS , LTXHWM and LTXEHWM > were set by default to 50 and 60% respectively. > For the latest version allowing dynamic logs (if set), LTXHWM and > LTXEHWM are set to 70 and 80% respectively. > > Cordialement, Regards, > > Khaled Bentebal > Directeur Général - ConsultiX > Président UGIF - User Group Informix France > IIUG - Board of Directors > Tél: 33 (0) 1 39 12 18 00 > Fax: 33 (0) 1 39 12 18 18 > Mobile: 33 (0) 6 07 78 41 97 > Email: khaled.bentebal@consult-ix.fr > Site Web: www.consult-ix.fr > > Le 18/11/11 06:38, CHUAN LU a écrit : > > Hi, > > > > when long transaction being rollback, could others execute SQL select > that > > don't write logical log? > > > > thanks. > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0016e64c3bc2d9ae3a04b2009d90