when does a commit occurs within an SQL file ?
in [DB2]
|
Prev: MDC on date column?
Next: Locked by agent id 0 huh!!
From: Toralf Förster on 19 Jul 2010 04:51 I'm wondering, whether the "nobody ...." comment in the following sql file (which is called via "$>db2 -t -f sql") : $>cat sql -- BEGIN delete from NEW ; -- nobody should to read from NEW at this point insert into NEW (select * from OLD) ; --END could be removed or if it is possible that somebody could read from table NEW between the delete and insert statement.
From: Mark A on 19 Jul 2010 10:27 "Toralf F�rster" <toralf.foerster(a)gmx.de> wrote in message news:i213mg$jf4$1(a)news.eternal-september.org... > I'm wondering, whether the "nobody ...." comment in the following sql file > (which is called via "$>db2 -t -f sql") : > > $>cat sql > -- BEGIN > delete from NEW > ; > -- nobody should to read from NEW at this point > insert into NEW (select * from OLD) > ; > --END > > could be removed or if it is possible that somebody could read from table > NEW between the delete and insert statement. Auto-commit is on by default, so commit happens after every statement.
From: Serge Rielau on 19 Jul 2010 19:19 On 7/19/2010 7:57 PM, Mark A wrote: > "Toralf F�rster"<toralf.foerster(a)gmx.de> wrote in message > news:i213mg$jf4$1(a)news.eternal-september.org... >> I'm wondering, whether the "nobody ...." comment in the following sql file >> (which is called via "$>db2 -t -f sql") : >> >> $>cat sql >> -- BEGIN >> delete from NEW >> ; >> -- nobody should to read from NEW at this point >> insert into NEW (select * from OLD) >> ; >> --END >> >> could be removed or if it is possible that somebody could read from table >> NEW between the delete and insert statement. > > Auto-commit is on by default, so commit happens after every statement. > > Aside from that in DB2 9.7 CURRENTLY COMMITTED for CS ISOLATION is on by default for all new databases. So there can be concurrent queries. These concurrent queries will see the "deleted" rows until the delete is committed. They will not see any INSERTed rows until that is committed. Cheers Serge -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab
From: Toralf Förster on 20 Jul 2010 04:44 Serge Rielau wrote: > Aside from that in DB2 9.7 CURRENTLY COMMITTED for CS ISOLATION is on by > default for all new databases. So there can be concurrent queries. > These concurrent queries will see the "deleted" rows until the delete is > committed. > They will not see any INSERTed rows until that is committed. Thx, so an sql file like this : $> cat sql delete ... ; insert .... ; commit ; called with this command: $>db2 +c -vt -f sql guarantees that nobody will see an empty table, isn't it ? -- MfG/Sincerely Toralf Förster pgp finger print: 7B1A 07F4 EC82 0F90 D4C2 8936 872A E508 7DB6 9DA3
From: Joachim Müller on 20 Jul 2010 07:00
On Jul 20, 1:19 am, Serge Rielau <srie...(a)ca.ibm.com> wrote: > On 7/19/2010 7:57 PM, Mark A wrote: > > > > > "Toralf Förster"<toralf.foers...(a)gmx.de> wrote in message > >news:i213mg$jf4$1(a)news.eternal-september.org... > >> I'm wondering, whether the "nobody ...." comment in the following sql file > >> (which is called via "$>db2 -t -f sql") : > > >> $>cat sql > >> -- BEGIN > >> delete from NEW > >> ; > >> -- nobody should to read from NEW at this point > >> insert into NEW (select * from OLD) > >> ; > >> --END > > >> could be removed or if it is possible that somebody could read from table > >> NEW between the delete and insert statement. > > > Auto-commit is on by default, so commit happens after every statement. > > Aside from that in DB2 9.7 CURRENTLY COMMITTED for CS ISOLATION is on by > default for all new databases. So there can be concurrent queries. > These concurrent queries will see the "deleted" rows until the delete is > committed. > They will not see any INSERTed rows until that is committed. > > Cheers > Serge > > -- > Serge Rielau > SQL Architect DB2 for LUW > IBM Toronto Lab- Hide quoted text - > > - Show quoted text - Hello Serge, indeed this was a feature. Our developer told me that he has to rewrite all procedure after upgrading to 9.7 and activate CURRENTLY COMMITTED , to do a commit after each sql. Is this true? But, it works as expected, we are very happy. Onother application has no more lockwaits after we upgrade to 9.7 Best regards, Joachim |