Transaction Logging issues
A site running many separate databases turned on transaction logging for only some of them and asked whether application code now needs explicit BEGIN WORK/COMMIT WORK. Consensus: no, it isn't required — without them each statement is its own singleton transaction — but you lose the ability to roll back multiple statements as a unit. Caveats raised: with logging on, some statements change behaviour — LOCK TABLE requires a transaction, UNLOCK TABLE can't be used, and opening a SELECT...FOR UPDATE cursor must occur inside an explicit transaction, so such loops do need BEGIN/COMMIT added. One poster also queried the claim that logging can't be enabled per-database, noting each database has its own log setting; that point wasn't followed up.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
WWe have a rather bizarre database environment which uses separate databases for separate processes. None of the db's had trans logging, so we turned it on for some of the most active. We just found out the hard way that you can't turn on trans. logging for a subset of our databases. We can fix that easily enough by listing all of the db's to turn on. The question now is this; Do we need to add begin/commit work statements in our code? Thanks. Kevin Struckhoff Customer Analytics Mgr. NewRoads Inc. kevin.struckhoff@newroads.com
You do not NEED the begin/commit work statements. Without them each statement is a singleton transaction within itself. However, without the BEGIN WORK/COMMIT WORK pair, you do not have the capability to rollback multiple update/insert/delete statements as a single unit nor can you decide post-fact to rollback an update/insert/delete based on other factors like that N rows were updated or deleted instead of M rows. Art S. Kagel ----- Original Message ----- From: Kevin Struc.... <kevin.struckhoff@newroads.com> At: 6/ 3 12:50 > WWe have a rather bizarre database environment which uses separate databases for > separate processes. None of the db's had trans logging, so we turned it on for > some of the most active. We just found out the hard way that you can't turn on > trans. logging for a subset of our databases. We can fix that easily enough by > listing all of the db's to turn on. > > The question now is this; Do we need to add begin/commit work statements in our > code? > > Thanks. > > Kevin Struckhoff > Customer Analytics Mgr. > NewRoads Inc. > kevin.struckhoff@newroads.com
KEVIN STRUC.... said: > WWe have a rather bizarre database environment which uses separate > databases for separate processes. None of the db's had trans logging, so > we turned it on for some of the most active. We just found out the hard > way that you can't turn on trans. logging for a subset of our databases. > We can fix that easily enough by listing all of the db's to turn on. > > The question now is this; Do we need to add begin/commit work statements > in our code? Ay caramba! The short answer is no, but my word that's an interesting application design... :o( -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche "Necrophilia means never having to say ... well, anything!" - Captain Pedantic "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco
ART KAGEL, .... said: > You do not NEED the begin/commit work statements. Without them each > statement > is a singleton transaction within itself. However, without the BEGIN > WORK/COMMIT WORK pair, you do not have the capability to rollback multiple > update/insert/delete statements as a single unit nor can you decide > post-fact to > rollback an update/insert/delete based on other factors like that N rows > were > updated or deleted instead of M rows. But since they clearly weren't doing this... :o) > ----- Original Message ----- > From: Kevin Struc.... <kevin.struckhoff@newroads.com> > At: 6/ 3 12:50 > >> WWe have a rather bizarre database environment which uses separate >> databases > for >> separate processes. None of the db's had trans logging, so we turned it >> on for >> some of the most active. We just found out the hard way that you can't >> turn on >> trans. logging for a subset of our databases. We can fix that easily >> enough by >> listing all of the db's to turn on. >> >> The question now is this; Do we need to add begin/commit work statements >> in > our >> code? >> >> Thanks. >> >> Kevin Struckhoff >> Customer Analytics Mgr. >> NewRoads Inc. >> kevin.struckhoff@newroads.com > > > > -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche "Necrophilia means never having to say ... well, anything!" - Captain Pedantic "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco
--0__=08BBE43BDFFA794B8f9e8a93df938690918c08BBE43BDFFA794B Content-type: multipart/alternative; Boundary="1__=08BBE43BDFFA794B8f9e8a93df938690918c08BBE43BDFFA794B" --1__=08BBE43BDFFA794B8f9e8a93df938690918c08BBE43BDFFA794B Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: quoted-printable Wanted to touch base on the "We just found out the hard way that you ca= n't turn on trans. logging for a subset of our databases." statement in this email.= You can turn on logging individual databases - is that what you meant? Each= database has it's own log settings. A bit confused..? Mark. Mark Scranton Principal Consultant/Teacher IBM Denver IBM Software Group - Data Management Office: 303-773-5067 Cell: 303-929-0914 email: mscranto@us.ibm.com = "ART KAGEL, ...." = <KAGEL@bloomberg. = net> = To Sent by: ids@iiug.org = forum.subscriber@ = cc iiug.org = Subj= ect Re: Transaction Logging issues = 06/03/2004 11:05 [3075] = AM = = = = = = You do not NEED the begin/commit work statements. Without them each statement is a singleton transaction within itself. However, without the BEGIN WORK/COMMIT WORK pair, you do not have the capability to rollback multi= ple update/insert/delete statements as a single unit nor can you decide post-fact to rollback an update/insert/delete based on other factors like that N ro= ws were updated or deleted instead of M rows. Art S. Kagel ----- Original Message ----- From: Kevin Struc.... <kevin.struckhoff@newroads.com> At: 6/ 3 12:50 > WWe have a rather bizarre database environment which uses separate databases for > separate processes. None of the db's had trans logging, so we turned = it on for > some of the most active. We just found out the hard way that you can'= t turn on > trans. logging for a subset of our databases. We can fix that easily enough by > listing all of the db's to turn on. > > The question now is this; Do we need to add begin/commit work stateme= nts in our > code? > > Thanks. > > Kevin Struckhoff > Customer Analytics Mgr. > NewRoads Inc. > kevin.struckhoff@newroads.com = --1__=08BBE43BDFFA794B8f9e8a93df938690918c08BBE43BDFFA794B Content-type: text/html; charset=US-ASCII Content-Disposition: inline Content-transfer-encoding: quoted-printable <html><body> <p>Wanted to touch base on the "<tt>We just found out the hard way= that you can't turn on<br> trans. logging for a subset of our databases.</tt>" statement in t= his email. You can turn on logging individual databases - is that what = you meant? Each database has it's own log settings. <br> <br> A bit confused..?<br> <br> Mark. <br> <br> <br> <br> Mark Scranton<br> Principal Consultant/Teacher<br> IBM Denver<br> <br> IBM Software Group - Data Management<br> Office: 303-773-5067<br> Cell: 303-929-0914<br> email: mscranto@us.ibm.com<br> <br> <br> <img src=3D"cid:10__=3D08BBE43BDFFA794B8f9e8a93df938@us.ibm.com" width=3D= "16" height=3D"16" alt=3D"Inactive hide details for "ART KAGEL, ..= .." <KAGEL@bloomberg.net>">"ART KAGEL, ...." <K= AGEL@bloomberg.net><br> <br> <br> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td style=3D"background-image:url(cid:20__=3D08BBE43= BDFFA794B8f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " wid= th=3D"40%"> <ul> <ul> <ul> <ul><b><font size=3D"2">"ART KAGEL, ...." <KAGEL@bloomberg= .net></font></b><font size=3D"2"> </font><br> <font size=3D"2">Sent by: forum.subscriber@iiug.org</font> <p><font size=3D"2">06/03/2004 11:05 AM</font></ul> </ul> </ul> </ul> </td><td width=3D"60%"> <table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">= <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D08BBE43BDFFA794B8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">To</font></div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D08BBE43BDFFA794B8f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">ids@iiug.org</font></td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D08BBE43BDFFA794B8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">cc</font></div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D08BBE43BDFFA794B8f9e8a93df938@us.ibm.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> </td></tr> <tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3= 0__=3D08BBE43BDFFA794B8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"= 1" width=3D"58" alt=3D""><br> <div align=3D"right"><font size=3D"2">Subject</font></div></td><td widt= h=3D"100%"><img src=3D"cid:30__=3D08BBE43BDFFA794B8f9e8a93df938@us.ibm.= com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">Re: Transaction Logging issues [3075]</font></td></t= r> </table> <table border=3D"0" cellspacing=3D"0" cellpadding=3D"0"> <tr valign=3D"top"><td width=3D"58"><img src=3D"cid:30__=3D08BBE43BDFFA= 794B8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt= =3D""></td><td width=3D"336"><img src=3D"cid:30__=3D08BBE43BDFFA794B8f9= e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><= /td></tr> </table> </td></tr> </table> <br> <tt>You do not NEED the begin/commit work statements. Without the= m each statement<br> is a singleton transaction within itself. However, without the BE= GIN<br> WORK/COMMIT WORK pair, you do not have the capability to rollback multi= ple<br> update/insert/delete statements as a single unit nor can you decide pos= t-fact to<br> rollback an update/insert/delete based on other factors like that N ro= ws were<br> updated or deleted instead of M rows.<br> <br> Art S. Kagel<br> <br> ----- Original Message -----<br> From: Kevin Struc.... <kevin.struckhoff@newroads.com>
Kevin Struckhoff <kevin.struckhoff@newroads.com> wrote: > We have a rather bizarre database environment which uses separate > databases for separate processes. None of the db's had trans > logging, so we turned it on for some of the most active. We just > found out the hard way that you can't turn on trans. logging for a > subset of our databases. We can fix that easily enough by listing > all of the db's to turn on. > > The question now is this; Do we need to add begin/commit work > statements in our code? Others have already commented on this - in general, no, but... The one thing I've not seen mentioned is that if your database has transactions, then you can only issue some statements within the scope of an explicit transaction, and some other statements won't work at all. F'r'instance: LOCK TABLE - must be in a transaction UNLOCK TABLE - may not be used in a transaction (and hence not in a transactional database, since the locking can only be done in a transaction). Those are the esoterica - the fundamental one for the average semi-decently written application is: OPEN cursor -- where cursor is FOR UPDATE This can only be done inside an explicit transaction. Of course, it is debatable whether the applications you are working with rise to the level of 'semi-decently written' :-) -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!"
If your code used declare select for update, You WILL need to add begin work/commit work around those loops. The program will fail when it hits anything like that. Art Kagel wrote: You do not NEED the begin/commit work statements. Without them each statement is a singleton transaction within itself. However, without the BEGIN WORK/COMMIT WORK pair, you do not have the capability to rollback multiple update/insert/delete statements as a single unit nor can you decide post-fact to rollback an update/insert/delete based on other factors like that N rows were updated or deleted instead of M rows. Art S. Kagel ----- Original Message ----- From: Kevin Struc.... At: 6/ 3 12:50 > WWe have a rather bizarre database environment which uses separate databases for > separate processes. None of the db's had trans logging, so we turned it on for > some of the most active. We just found out the hard way that you can't turn on > trans. logging for a subset of our databases. We can fix that easily enough by > listing all of the db's to turn on. > > The question now is this; Do we need to add begin/commit work statements in our > code? > > Thanks. > > Kevin Struckhoff > Customer Analytics Mgr. > NewRoads Inc. > kevin.struckhoff@newroads.com
ramy@chubb.com said: > If your code used declare select for update, You WILL need to add begin > work/commit work around those loops. The program will fail when it hits > anything like that. I seem to be infecting people with my urge for not reading the original post properly. > Art Kagel wrote: > > You do not NEED the begin/commit work statements. Without them each > statement > is a singleton transaction within itself. However, without the BEGIN > WORK/COMMIT WORK pair, you do not have the capability to rollback multiple > update/insert/delete statements as a single unit nor can you decide > post-fact > to > rollback an update/insert/delete based on other factors like that N rows > were > updated or deleted instead of M rows. > > Art S. Kagel > > ----- Original Message ----- > From: Kevin Struc.... > At: 6/ 3 12:50 > >> WWe have a rather bizarre database environment which uses separate > databases > for >> separate processes. None of the db's had trans logging, so we turned it > on > for >> some of the most active. We just found out the hard way that you can't > turn > on >> trans. logging for a subset of our databases. We can fix that easily > enough > by >> listing all of the db's to turn on. >> >> The question now is this; Do we need to add begin/commit work statements > in > our >> code? >> >> Thanks. >> >> Kevin Struckhoff >> Customer Analytics Mgr. >> NewRoads Inc. >> kevin.struckhoff@newroads.com > > -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco