Re: Transaction (was: Re: [Dbi-interbase-devel] Re: fixing InterBase.pm and IBPerl.pm for transactions)

[Mark D. A. <md...@di...>]

> Now a transaction is active only when needed, and committed as soon as
> the operation is done, when AutoCommit is On.

cool.
there are no sourceforge bugs, and the release notes just say "severe known problems are fixed".
how close does it "feel" to you?

> > > Now we use isc_commit_retaining(), which doesn't close an active
> > > transaction. this should give better performance for we don't have to
> > > start a new transaction after a commit. We use isc_commit_transaction()
> > > only in dbd_db_disconnect().
> > 
> > this is likely to create lock timeouts in other connections, depending on what transaction
> > parameters you have. for greater throughput, you want to have the smallest transaction
> > profile possible. InterBase may have some pseudo-magical multi-versioning capabilities,
> > but that doesn't alter the realities of ACID semantics.
> 
> when AutoCommit is Off, for now I still use isc_commit_retaining().
> Could you provide test cases to show that isc_commit_retaining() will
> cause locking problems?

i don't know enough about how the interbase engine works to know what would
show it, but this behavior certainly *should* cause starvation.
the semantics of retaining is that the client connection retains locks on objects
involved in the committed transaction. so if every commit retains locks, the client
will gradually accumulate more and more locks, for as long as it lives.
this will eventually cause either lock timeouts or deadlock, assuming that reasonable
isolation levels are chosen.

i should think that to retain locks should be an option to commit, and by default off.
the use cases for proper use of it are not common.
if for some reason you know that there will be little concurrency, you can get
greater throughput for yourself by retaining locks (because you needn't refresh
the values invalidated by the commit).

> Take a look at the latest code (0.20.2), alternatively you can do cvs
> checkout:

i scanned it in the web cvs; it looked about right -- but i think i'll wait before
trying a release before you announce that it is not totally radioactive, or at least
indicate things you know are still broken....

> At this point, I have some notes that I'd like to discuss:
> 1. other drivers (eg Oracle) use SET TRANSACTION sql command to specify
> isolation level. 

ib has a SET TRANSACTION as well, through i don't think there is any
need for us to use it, since we can set all the flags via options.
below is some xs code i sent karwin for use in IBPerl for creating transactions
with arbitrary flags (currently IBPerl does not implement any parameters
for txn creation).

> Because the hierarchy is: database handle -> transaction handle ->
> statement handle, it seems that changing isolation level affects the
> whole statement handle (within the same DBI dbh).

i wouldn't say that is the hierarchy.
after all statements can be prepared and cached, and used in multiple transactions.
if i were going to turn autocommit off, i would want to be able to have multiple
transaction objects at once, and create them myself with parameters of my choosing,
and pass them in to execute().

> 2. I have a specific problem when running Jochen's test scripts, for
> example t/50chopblanks.t. 
> After the second $sth->finish, it does a DROP TABLE: $dbh->do("DROP
> TABLE ...");
> It is fine with mysql, but not with InterBase. 
> Within dbd_st_finish(), it only calls isc_dsql_free_statement() with
> DSQL_close as the last arg,
> ie, it only _close_ the cursor, not drop the statement.
> Statement is actually drop within dbd_st_destroy, this is what I read
> from DBI::DBD.
> Is it a normal behavior (so we can say that the test script should be
> fixed), or there's something wrong with my implementation? Have you seen
> the same behavior with other drivers?

i thought it was generally true that you can't drop a table (or do any ddl)
if you don't have a dedicated connection to the database?
maybe even a dedicated txn?
could it be that? i don't know the specific constraints.

-mda

/* for insertion in IBPerl.xs in IB_start_transaction */
 /*
 * mda 2000-jul-25: start support for transaction parameters.
 * The default tpb is in ApiGuide p69:
          static char isc_tpb[] = {
     isc_tpb_version3,
            isc_tpb_write,
            isc_tpb_concurrency,
            isc_tpb_wait};
 * The tpb is a hack data structure, with each option being a byte.
 * Here we treat it as a null-terminated string. 
 * Order is unimportant, except for reservation (I think).
 */
 static char isc_tpb[10];
 {
   char* opts = isc_tpb;
   unsigned int len;

          SV** access_sv = hv_fetch(trhash, "Access", strlen("Access"), 0);
   char* access = (access_sv != (SV**)NULL && SvPOK(*access_sv) && (len = SvCUR(*access_sv)) > 0) ?
     SvPV_nolen(*access_sv) : "WRITE";

          SV** isolation_sv = hv_fetch(trhash, "Isolation", strlen("Isolation"), 0);
   char* isolation = (isolation_sv != (SV**)NULL && SvPOK(*isolation_sv) && (len = SvCUR(*isolation_sv)) > 0) ?
     SvPV_nolen(*isolation_sv) : "CONCURRENCY";

          SV** resolution_sv = hv_fetch(trhash, "Resolution", strlen("Resolution"), 0);
   char* resolution = (resolution_sv != (SV**)NULL && SvPOK(*resolution_sv) && (len = SvCUR(*resolution_sv)) > 0) ?
     SvPV_nolen(*resolution_sv) : "WAIT";

          /* TODO: reservation is per-table, so a simple hash value on the transaction isn't enough.
      Each is a triplet of:
      {isc_tpb_shared, isc_tpb_protected}
      {isc_tpb_lock_read, isc_tpb_lock_write}
      null-terminated tablename
    */

   *opts++ = isc_tpb_version3;

   if (streq(access, "WRITE")) {*opts++ = isc_tpb_write;}
   else if (streq(access, "READ")) {*opts++ = isc_tpb_read;}
   else {Error_String(trhash, "Illegal value for Access"); RETVAL = FAILURE; goto end;}

   if (streq(isolation, "CONCURRENCY")) {*opts++ = isc_tpb_concurrency;}
   else if (streq(isolation, "READ_COMMITTED_VERSION")) {*opts++ = isc_tpb_read_committed; *opts++ isc_tpb_rec_version;}
   else if (streq(isolation, "READ_COMMITTED_NO_VERSION")) {*opts++ = isc_tpb_read_committed; *opts++ isc_tpb_no_rec_version;}
   else if (streq(isolation, "CONSISTENCY")) {*opts++ = isc_tpb_consistency;}
   else {Error_String(trhash, "Illegal value for Isolation"); RETVAL = FAILURE; goto end;}

   if (streq(resolution, "WAIT")) {*opts++ = isc_tpb_wait;}
   else if (streq(resolution, "NOWAIT")) {*opts++ = isc_tpb_nowait;}
   else {Error_String(trhash, "Illegal value for Resolution"); RETVAL = FAILURE; goto end;}
   
   *opts++ = (char)0;
 }

 isc_start_transaction(tr->status,
     (isc_tr_handle *) &(tr->handle),
     1,
     (isc_db_handle *) &(tr->db->handle),
     (unsigned short)strlen(isc_tpb),
     isc_tpb);