Info require about COMMIT
A user on IDS 11.10 with a buffered-logging database asked whether a standalone UPDATE without BEGIN WORK/COMMIT is actually committed. Answer: yes — a singleton insert/update/delete outside an explicit transaction is implicitly wrapped in BEGIN WORK/COMMIT, though with buffered logging it isn't hardened until the log buffer is flushed (e.g. at a checkpoint). Follow-up restore tests were also explained: a TRUNCATE captured in the logs correctly emptied the table on restore, and a restore showing only 50,000 of 100,000 rows was due to buffered logging plus the last log not being backed up (ontape -c only archives completed logs; use onmode -l to switch). Art Kagel recommended unbuffered logging.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello, Basic question about commit! Informix version 11.10 database in Buffered login mode. update emp set ename='vikas' where empid=2; 1 rows updated. pls let me know as i have not started the sql with begin work neighter ended it with commit . will this transaction be commited in database . now if a checkpoint is completed and this transaciton is written on to the disk and then the database abruptly went down (power failure)then what will happen to this transaction, will it be rolled forwar or rollback in fast recovery. Does informix performs explicit commits if the "begin work" is not used do we have to mention "commit" in order to commit/complete the transction. Thanking you all in advance !
In IDS a singleton insert, update, or delete statement issued without a BEGIN WORK; is a transaction unto itself. It commits automatically if it succeeds and rolls back if it fails. It will be treated as if it were bracketed by a BEGIN WORK and COMMIT WORK. Not to worry, your data is safe. Art S. Kagel Oninit On Thu, May 8, 2008 at 10:37 AM, VIKAS HIVARKAR <vikashivarkar3@gmail.com> wrote: > Hello, > > Basic question about commit! > > Informix version 11.10 > database in Buffered login mode. > > update emp > set ename='vikas' where empid=2; > > 1 rows updated. > > pls let me know as i have not started the sql with begin work neighter > ended > it with commit . will this transaction be commited in database . > now if a checkpoint is completed and this transaciton is written on to the > disk and then the database abruptly went down (power failure)then what will > happen to this transaction, will it be rolled forwar or rollback in fast > recovery. > > Does informix performs explicit commits if the "begin work" is not used > do we have to mention "commit" in order to commit/complete the transction. > > Thanking you all in advance ! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >
When you execute a statement which is not contained within an explicit BEGIN WORK/COMMIT, then there is an implicit BEGIN WORK/COMMIT which is= wrapped around that statement. So in your case, the operation is equivalent to. BEGIN WORK; update emp set ename=3D'vikas' where empid=3D2; COMMIT WORK; However, since you are using buffered logging, then you need to underst= and that the operation is not hardened until the log buffer flush occurs. = A checkpoint would cause a log flush. ------------------------------------- Madison Pruet, STSM IDS Replication Architect = "VIKAS HIVARKAR" = <vikashivarkar3@g = mail.com> = To Sent by: ids@iiug.org = ids-bounces@iiug. = cc org = Subj= ect Info require about COMMIT [1203= 9] 05/08/2008 09:37 = AM = = = Please respond to = ids@iiug.org = = = Hello, Basic question about commit! Informix version 11.10 database in Buffered login mode. update emp set ename=3D'vikas' where empid=3D2; 1 rows updated. pls let me know as i have not started the sql with begin work neighter ended it with commit . will this transaction be commited in database . now if a checkpoint is completed and this transaciton is written on to = the disk and then the database abruptly went down (power failure)then what = will happen to this transaction, will it be rolled forwar or rollback in fas= t recovery. Does informix performs explicit commits if the "begin work" is not used= do we have to mention "commit" in order to commit/complete the transcti= on. Thanking you all in advance ! ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =
Thank you Mr Kagel !
pls look in to following activity .
i took a level 0 backup (bkp1) on my test1 server
created a table salary & inserted 100000 rows
backed up the logical logs using ontape -a (logbkp1)
i restore this bkp1 on a different ie test2 server
applied the logical logs in restoration
did select count(*) from salary gave me 100000 rows
result - restoration sucessfull.
Now i started ontape -c on test1 server
truncated table salary
created table restore & inserted 100000 rows (confirmed with select count)
interruped the ontape -c using cntrlc ( clogbkp1)
then backedup the next log in sequence using ontape -a (clogbkp2)
now i restore the bkp1 on test2
applied initial logical logs ie (logbkp1) ### it asked for log no 1153 - 1156
which were present in logbko1.
then i applied clogbkp1
then i applied clogbkp2
then said no for the any further tape and recovery of physical & logical logs
completed
Logical Recovery Complete.
85 Committed, 1 Rolled Back, 0 Open, 0 Bad Locks
when i checked for restore table i found it was created with 100000 rows
but the salary table showed count as "0".
Was this because of "truncate table" issued after the ontape -c was started .
sorry for such big explanation.
Yes, the TRUNCATE TABLE SALARY; destroyed the contents of the salary table
on the test1 server and that command was included in the logical logs that
you applied on test2 after the second restore. All is as it SHOULD be. Not
necessarily what you expected, but works-as-designed.
Art S. Kagel
Oninit
On Thu, May 8, 2008 at 11:14 AM, VIKAS HIVARKAR <vikashivarkar3@gmail.com>
wrote:
> Thank you Mr Kagel !
>
> pls look in to following activity .
>
> i took a level 0 backup (bkp1) on my test1 server
> created a table salary & inserted 100000 rows
> backed up the logical logs using ontape -a (logbkp1)
>
> i restore this bkp1 on a different ie test2 server
> applied the logical logs in restoration
> did select count(*) from salary gave me 100000 rows
> result - restoration sucessfull.
>
> Now i started ontape -c on test1 server
> truncated table salary
> created table restore & inserted 100000 rows (confirmed with select count)
> interruped the ontape -c using cntrlc ( clogbkp1)
> then backedup the next log in sequence using ontape -a (clogbkp2)
>
> now i restore the bkp1 on test2
> applied initial logical logs ie (logbkp1) ### it asked for log no 1153 -
> 1156
> which were present in logbko1.
> then i applied clogbkp1
> then i applied clogbkp2
> then said no for the any further tape and recovery of physical & logical
> logs
> completed
>
> Logical Recovery Complete.
>
> 85 Committed, 1 Rolled Back, 0 Open, 0 Bad Locks
>
> when i checked for restore table i found it was created with 100000 rows
> but the salary table showed count as "0".
>
> Was this because of "truncate table" issued after the ontape -c was started
> .
>
> sorry for such big explanation.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
yes ! i am learning the desing with some practicle's
in another activity i did the following steps
1.took level 0 bkp (bkp1) on test1 server
2.started ontape -c in one session on test1 server
3.created table salary and inserted 100000 rows
4.interruped the ontape -c with cntrl^c
this showed
Interrupt received ...
Logbackup interrupted. Server is down - function close log backup failed code
-1 errno 0
Please label this tape as number 1 in the log tape sequence.
This tape contains the following logical logs:
1533 - 1536
5.i now checked in onstat -l and found that 1533 -1536 were backed up and the
next log 1537 was with size "0".
6.logical log tape (cbkp1 containing 1533 - 1536)
6.then i restored the level 0 (bkp1) on test2 server
applied the log tape containing 1533 - 1536
7.said no to any further tape
8.took the db online using onmode -m
9.checked select count(*) from salary on test2 db server
10.i got the result as 50000 rows .
Pls explain what went wrong.i did not backed up the next log ie 1537 because
it was of 0 size.
Please also let me know which is the most preferred logical log backup
approach used .( sure it depends upon the requirement)but still would like to
hear ur comments.
IDS only archives logical logs when they have completed or are manually
switched. If you did not run onmode -l to switch logs then the last logical
log, 1536?, was not backed up. Also, even if it was, you stated before, IB,
that the database you are testing on was created with BUFFERED logging.
That means that the logical log buffers are ONLY flushed to the physical
logical logs on disk when the buffer is full. If your logical log buffers
(LOGBUFF) are large enough to hold 50,000 rows of data, the remaining data
was never flushed to disk and so never archived to the tape/file by ontape
-c! That's the reason I STRONGLY recommend ONLY using unbuffered logging
modes! In those modes the logical log buffer is flushed to disk when full
or when a commit record is written to it. In that case, the commit would
have caused the final buffer to flush to disk. Now, ontape -c still will
not pick up the last logical log until you force it to complete with onmode
-l or i happens to fill causing a log switch.
Art S. Kagel
Oninit
On Thu, May 8, 2008 at 11:47 AM, VIKAS HIVARKAR <vikashivarkar3@gmail.com>
wrote:
> yes ! i am learning the desing with some practicle's
>
> in another activity i did the following steps
>
> 1.took level 0 bkp (bkp1) on test1 server
> 2.started ontape -c in one session on test1 server
> 3.created table salary and inserted 100000 rows
> 4.interruped the ontape -c with cntrl^c
> this showed
> Interrupt received ...
>
> Logbackup interrupted. Server is down - function close log backup failed
> code
> -1 errno 0
>
> Please label this tape as number 1 in the log tape sequence.
>
> This tape contains the following logical logs:
>
> 1533 - 1536
>
> 5.i now checked in onstat -l and found that 1533 -1536 were backed up and
> the
> next log 1537 was with size "0".
> 6.logical log tape (cbkp1 containing 1533 - 1536)
> 6.then i restored the level 0 (bkp1) on test2 server
> applied the log tape containing 1533 - 1536
> 7.said no to any further tape
> 8.took the db online using onmode -m
> 9.checked select count(*) from salary on test2 db server
>
> 10.i got the result as 50000 rows .
>
> Pls explain what went wrong.i did not backed up the next log ie 1537
> because
> it was of 0 size.
>
> Please also let me know which is the most preferred logical log backup
> approach used .( sure it depends upon the requirement)but still would like
> to
> hear ur comments.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>