[comp.databases] Transactions, logging, and Online

devin@qip.UUCP (Devin Wala) (11/10/90)

We have run across an oddity in INFORMIX ONLINE.  

1) We create a database with logging turned on.  So far so good.

   A) We can then LOAD data into the database through isql without
   issuing BEGIN/COMMIT/ROLLBACK WORK.  Informix does this without
   returning an error, even though the LOAD statement is clearly
   not in a transaction.

   B) We can write ESQL/C programs which use INSERT, DELETE and UPDATE
   without issuing a BEGIN/COMMIT/ROLLBACK WORK.  The programs run
   though to completion without ever once returning an error code.

   C) If we write the ESQL/C program to use DECLARE CURSOR .. FOR UPDATE
   Informix will return a "NOT IN TRANSACTION" error like one would
   expect.

2) After one particular program we have which does not have BEGIN/COMMIT/
   ROLLBACK is run against a database with logging turned on, Informix 
   is left in a crippled state.  
   
   Any current users of Informix are able to continue working.  However, 
   any users wishing to start running Informix from this point on receive 
   the classic "An invalid message has been returned by a sqlexec process". 



We are running INFORMIX ONLINE vers UD4 on a PYRAMID 9846 and
thought others might be interested.

-Devin Wala


-- 
------------------------------------------------------------------------------
   o oo o                                           
  o \||/ o     Anasazi, Inc                         
 o--****--o      7500 N. Dreamy Draw Drive, Ste 120 
 o--****--o      Phoenix, AZ. 85020                 
  o /||\ o                                         
   o oo o   {asuvax or mcdphx}!anasaz!devin (Devin Wala) (602)943-4449

cortesi@informix.com (David Cortesi) (11/13/90)

In article <5394@qip.UUCP> devin@qip.UUCP (Devin Wala) writes:
>
>We have run across an oddity in INFORMIX ONLINE.  
>
>1) We create a database with logging turned on.  So far so good.
>
	[and can then LOAD, INSERT, DELETE and UPDATE w/o using
	 BEGIN/COMMIT -- but cannot OPEN an update cursor w/o BEGIN]

This is intentional.  As it says on page 30 of the OnLine Programmer's
Manual,

	"If you are not using a MODE ANSI database, and if you
	do not explicitly use [BEGIN and COMMIT] each SQL statement
	is an implicit, or singleton transaction. Successful singleton
	transactions are automatically committed. Unsuccessful SQL
	statements are automatically rolled back."

The statements you report as working were all treated as singleton
tranactions.  When you put in explicit BEGIN, COMMIT and ROLLBACK
statements, you can define the bounds of transactions.  For example
if you want a series of DELETEs treated as a single transaction to
be committed or rolled back as a unit, you need to write BEGIN WORK
ahead of the first and COMMIT after the last.

Or if you prefer ANSI-compliant transaction management (explicit
BEGIN WORK not required; explicit COMMIT WORK required to end any
transaction) you can have that by setting up the database with
LOG MODE ANSI.  That is the meaning of the reference to MODE ANSI
in the above quote.