dbi-interbase-devel Mailing List for DBD::InterBase (Page 9)
Status: Beta
Brought to you by:
edpratomo
Menu
▾
▴
dbi-interbase-devel — Development list for DBD::InterBase
You can subscribe to this list here.
| 2000 |
Jan
|
Feb
|
Mar
|
Apr
|
May
|
Jun
|
Jul
(3) |
Aug
(44) |
Sep
(33) |
Oct
(36) |
Nov
(1) |
Dec
(1) |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2001 |
Jan
(4) |
Feb
(3) |
Mar
(2) |
Apr
(1) |
May
(4) |
Jun
(15) |
Jul
(24) |
Aug
(8) |
Sep
(4) |
Oct
(5) |
Nov
(1) |
Dec
(4) |
| 2002 |
Jan
|
Feb
|
Mar
|
Apr
(7) |
May
(7) |
Jun
|
Jul
|
Aug
(3) |
Sep
|
Oct
|
Nov
|
Dec
(1) |
| 2003 |
Jan
(1) |
Feb
|
Mar
(6) |
Apr
|
May
|
Jun
|
Jul
|
Aug
(1) |
Sep
|
Oct
|
Nov
|
Dec
|
|
From: Edwin P. <ed....@co...> - 2000-08-16 01:59:18
|
Michael Samanov wrote:
> EP> Michael Samanov wrote:
> >>
> >> $dbh->do("INSERT INTO ORDERS (user_id,comment) VALUES (?,?)", undef, 1, '')
It's already fixed now.
> >> nor even
> >> $dbh->do('INSERT INTO ORDERS (user_id,comment) VALUES (1,"")')
>
> EP> This really suprised me. Exchanging the double quote with single quote
> EP> works just fine:
> $dbh->>do("INSERT INTO ORDERS (user_id, comment) VALUES (1,'')");
> EP> I'll fix this bug, thanks for the report.
>
> Warning: http://www.interbase.com/open/research/art_60dialects.html.
Yes, this messy is because of dialect. Thanks for the link.
> My DSN was
> "DBI:InterBase:database=/usr/interbase/price.gdb;host=localhost;ib_dialect=3;ib_charset=WIN1251",
> maybe old and new drivers use different conventions about the dialect?
No. change the ib_dialect to 1, and it will work, but of course the new
v 6.0 datatypes won't work instead :-(
Try with isql with the dialect default set to 3.
> Yet other thing: I can't get working the below statement neither with
> old nor new driver.
> "SELECT ... WHERE UPPER(GOODS_NAME COLLATE PXW_CYRL) like UPPER(? COLLATE PXW_CYRL)"
> It says 2 <- prepare= undef at DBI.pm line 908.
> !! ERROR: -1 'Dynamic SQL Error
> -SQL error code = -804
> -Data type unknown
I don't know the answer yet :-)
Later,
Edwin.
|
|
From: Edwin P. <ed....@co...> - 2000-08-11 22:20:54
|
Edwin Pratomo wrote: > Actually I wanted to upload the 0.20.5 tarball which contains the new > Makefile.PL that recognize the final IB 6.0, but I got permission denied > error from the sourceforge's upload server, maybe it's forbidden to > upload two different versions just within a few minutes. Oops now it works.. Rgds, Edwin. |
|
From: Edwin P. <ed....@co...> - 2000-08-11 22:10:17
|
Edwin Pratomo wrote: > But you can get the new Makefile.PL on the CVS repository. > I've used it to build my DBD-InterBase on a machine running the final IB > 6.0, and passed the "make test" successfully. Hmm.. to be more precise, not 100%, see NOTES :-) Rgds, Edwin. |
|
From: Edwin P. <ed....@co...> - 2000-08-11 22:07:59
|
"Mark D. Anderson" wrote:
>
> i just downloaded and tried the 20.3 tarball.
> it built successfully, once i edited Makefile.PL to use /opt/interbase instead
> of /usr/interbase (a change between beta and final linux release).
>
> however, i couldn't even get a connect to work:
>
> this line
> my $dbh = DBI->connect($OUT_DBI_DSN,'','',{RaiseError=>1}) || die "cannot connect to '$OUT_DBI_DSN': ",
> $DBI::errstr;
>
> got me this:
> DBI->connect failed: lock conflict on no wait transaction at snapshot.pl line 180
I've just put 0.20.4 tarball which solves some previous problems dealing
with ChopBlanks and blobs.
Actually I wanted to upload the 0.20.5 tarball which contains the new
Makefile.PL that recognize the final IB 6.0, but I got permission denied
error from the sourceforge's upload server, maybe it's forbidden to
upload two different versions just within a few minutes.
But you can get the new Makefile.PL on the CVS repository.
I've used it to build my DBD-InterBase on a machine running the final IB
6.0, and passed the "make test" successfully.
Rgds,
Edwin.
|
|
From: Edwin P. <ed....@co...> - 2000-08-10 07:09:07
|
"Mark D. Anderson" wrote: > > more information -- > > earlier today i also got the "lock conflict on no wait transaction" message even from > isql, so the new DBD-InterBase was not the only one getting it. > .......... Thanks for your reports, Mark, keep 'em coming :-) I'll take a closer look at this problem right after I've finished with blob features. A couple of hours ago I committed some changes to the CVS repository, which fix some problems with ChopBlanks. Now it passes the bindparam and chopblanks tests perfectly. Tonight I'll work on the blob features. > must have something to do with transaction parameters, which is what got me started > on this thread anyway :). hopefully soon InterBase.pm will expose complete control Yes, sounds like that. > over transaction parameters, so i can actually experiment with that, rather than > poking at things with a very long stick.... Could you send me your test scripts? I'd like to reproduce the problem here. Rgds, Edwin. |
|
From: Mark D. A. <md...@di...> - 2000-08-10 05:06:24
|
more information -- earlier today i also got the "lock conflict on no wait transaction" message even from isql, so the new DBD-InterBase was not the only one getting it. i just re-ran my script, and i got: no permission for read-write access to database /home/interbase/data/snap/snap4.gdb and guess what? i got the same thing from isql just now also! like this: SQL> connect /home/interbase/data/snap/snap4.gdb; Statement failed, SQLCODE = -551 no permission for read-write access to database /home/interbase/data/snap/snap4.gdb however, my various ibconsole-equivalents (ib_wisql, marathon, etc.) have no problem connecting. also, my very same perl script using the old DBD::InterBase (based on ibperl) works fine. so what is it that isql and the new InterBase.pm have in common, and that ib_wisql and the old InterBase.pm have in common? must have something to do with transaction parameters, which is what got me started on this thread anyway :). hopefully soon InterBase.pm will expose complete control over transaction parameters, so i can actually experiment with that, rather than poking at things with a very long stick.... btw, i also tracked down what was responsible for the "lock conflict" message -- it was due to connecting to the database with ibobject's IB_WISQL. it must open up a transaction with some obnoxious transaction parameters as soon as it connects. i've looked for further documentation on both the messages above, and not succeeded. -mda |
|
From: Mark D. A. <md...@di...> - 2000-08-08 14:37:14
|
> I haven't tested it with perl 5.6 and the final InterBase 6.0. > Have you successfully used it with perl 5.6 and IB 6.0 beta? didn't try, but this didn't appear to be a message that would be affected by that. > > > btw, how awful would it be to name this module something other than DBD::InterBase, > > seeing as how there are two other such module out there already? > > it makes it hard to have multiple alternatives installed. > > For testing purposes, you should 'use blib;' before 'use DBI;' in your > scripts, and make sure your scripts located within the directory > structure of the uncompressed tarball. yeah, that is what i did, though is still can't do "make install" because of the overwrite. > btw, what are the two other modules you mention above? > your own earlier one over ibperl, and the other project on sourceforge. -mda |
|
From: Edwin P. <ed....@co...> - 2000-08-08 06:02:03
|
"Mark D. Anderson" wrote: > > i just downloaded and tried the 20.3 tarball. > it built successfully, once i edited Makefile.PL to use /opt/interbase instead > of /usr/interbase (a change between beta and final linux release). > Carp::croak (package 'Carp') at /home/buildsite/local/perl-5.6.0/lib/5.6.0/Carp.pm, line 119 > DBI::connect (package 'DBI') at /home/buildsite/local/perl-5.6.0/lib/site_perl/5.6.0/i686-linux/DBI\ I haven't tested it with perl 5.6 and the final InterBase 6.0. Have you successfully used it with perl 5.6 and IB 6.0 beta? > btw, how awful would it be to name this module something other than DBD::InterBase, > seeing as how there are two other such module out there already? > it makes it hard to have multiple alternatives installed. For testing purposes, you should 'use blib;' before 'use DBI;' in your scripts, and make sure your scripts located within the directory structure of the uncompressed tarball. btw, what are the two other modules you mention above? Rgds, Edwin. |
|
From: Mark D. A. <md...@di...> - 2000-08-08 05:02:52
|
i just downloaded and tried the 20.3 tarball.
it built successfully, once i edited Makefile.PL to use /opt/interbase instead
of /usr/interbase (a change between beta and final linux release).
however, i couldn't even get a connect to work:
this line
my $dbh = DBI->connect($OUT_DBI_DSN,'','',{RaiseError=>1}) || die "cannot connect to '$OUT_DBI_DSN': ",
$DBI::errstr;
got me this:
DBI->connect failed: lock conflict on no wait transaction at snapshot.pl line 180
at:
Carp::croak (package 'Carp') at /home/buildsite/local/perl-5.6.0/lib/5.6.0/Carp.pm, line 119
DBI::connect (package 'DBI') at /home/buildsite/local/perl-5.6.0/lib/site_perl/5.6.0/i686-linux/DBI\
.pm, line 385
main::main (package 'main') at snapshot.pl, line 180
(top) (package 'main') at snapshot.pl, line 234
btw, how awful would it be to name this module something other than DBD::InterBase,
seeing as how there are two other such module out there already?
it makes it hard to have multiple alternatives installed.
-mda
|
|
From: Mark D. A. <md...@di...> - 2000-08-04 23:49:12
|
> Silly me. I've just recalled that the DBI commmit() method is run > against database handle object! ($dbh->commit). > so, we violate the DBI spec if we make the transaction per statement > basis. well, it wasn't my idea :). how is this: - connect() takes options to specify the parameters used for any transactions automatically created by the driver (whether autocommit or not) - connect() takes an option called "autotxn", which is only meaningful if autocommit is off. - connect() starts an automatic transaction unless (autocommit,autotxn) = (off,off) - begin_txn($params) is a new interface function for explicit transaction control (off,off). - commit() takes an option to retain locks, and by default does not - commit() is illegal if autocommit is on. if autotxn is on, it starts a new transaction afterwards, if off it does not. - rollback() is analogous. - execute() and do() require an explicit transaction object if (off,off). illegal to provide one otherwise. -mda |
|
From: Edwin P. <ed....@co...> - 2000-08-04 17:12:52
|
Edwin Pratomo wrote: > > > > 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). > > I'm aware of this. the problem is where I should put these options. > Current implementation puts the interbase transaction handle within the > imp_dbh structure. Changing transaction parameters will affect all > statements from the same $dbh (DBI database handle). > Alternatively, we can put the trans handle within the imp_sth structure. > the consequence is there will be a separate transaction for each > statement, but we gain flexibility, because we can pass the transaction > parameters to the DBI prepare() method. Silly me. I've just recalled that the DBI commmit() method is run against database handle object! ($dbh->commit). so, we violate the DBI spec if we make the transaction per statement basis. Rgds, Edwin. |
|
From: Edwin P. <ed....@co...> - 2000-08-04 10:50:47
|
"Mark D. Anderson" wrote: > cool. > there are no sourceforge bugs, and the release notes just say "severe known problems are fixed". > how close does it "feel" to you? Never take it for granted, try it by yourself :-) That's my laziness. But it's true that I've fixed previous known problems (coredump). > > 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'll write some test scripts for this. Currently I'm focusing on fixing problems encountered when running the test scripts. Getting basic functionalities to work fine, and clean memory cleanups are my near target. The latest change was added support for InterBase 6.0 data types: date, time, timestamp, and 64 bit numeric. > > 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). I'm aware of this. the problem is where I should put these options. Current implementation puts the interbase transaction handle within the imp_dbh structure. Changing transaction parameters will affect all statements from the same $dbh (DBI database handle). Alternatively, we can put the trans handle within the imp_sth structure. the consequence is there will be a separate transaction for each statement, but we gain flexibility, because we can pass the transaction parameters to the DBI prepare() method. > > 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(). I'll address this issue after the test scripts are ready. Rgds, Edwin. |
|
From: Mark D. A. <md...@di...> - 2000-08-03 19:23:40
|
> 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 add -- that is, if there is more than one connection trying to perform writes. if only one connection is doing writes, this should work -- again, assuming appropriate transaction parameters are set by all connections so they can choose what isolation to use (i.e. so the readonly connections will see committed changes even though another is known to be pending). -mda |
|
From: Mark D. A. <md...@di...> - 2000-08-03 07:35:04
|
> 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);
|
|
From: Edwin P. <ed....@co...> - 2000-07-31 21:47:13
|
"Mark D. Anderson" wrote:
>
> > > When AutoCommit is on, it appears its current behavior is incorrect.
> > > IMHO it should:
> > > 1. Not maintain an always active transaction.
Now a transaction is active only when needed, and committed as soon as
the operation is done, when AutoCommit is On.
>
> > > 2. Not use the _commit() function at all, as it stands.
> > > 3. For do(), it should create a new transaction right before, and commit it right after.
Yes, the do() method (in InterBase.xs) has its own way instead of let
the DBI take the conservative way (dbd_st_prepare - dbd_st_execute).
> > 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?
> > > Using the default IBPerl transaction should be fine.
> > > 4. For execute() of a non-select, it should do the same thing as do().
> > > 5. For execute() of a select (or select-like operation), it should create a new read-only
> > > transaction right before the underlying execute. It should then commit that transaction
> > > in fetch() and finish() (being careful to do the right thing if fetch calls finish).
> >
> > execute() must be run on an active transaction.
>
> that is what happens in my above pseudo-code.
>
> i might note that the above is not just my intellectual analysis, this is based on looking at
> how other rdbms dbd drivers work.
Thanks. The recent modification is based on DBD::Pg's route to handle
the AutoCommit attr.
Take a look at the latest code (0.20.2), alternatively you can do cvs
checkout:
cvs -z3
-d:pserver:ano...@cv...:/cvsroot/dbi-interbase
co dbi-interbase
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.
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).
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?
Rgds,
Edwin.
|
|
From: Mark D. A. <md...@di...> - 2000-07-28 16:11:52
|
> > When AutoCommit is on, it appears its current behavior is incorrect.
> > IMHO it should:
> > 1. Not maintain an always active transaction.
>
> I'm afraid that we can't do this:
> 1. ANSI/ISO standard for SQL states that a database connection is always
> _in_ active transaction.
i realize you have to have a transaction when you send operations.
i'm suggesting that for cleaner code, i don't see the point in maintaining a
state variable ('ib_trans_handle') when in autocommit mode, since in that mode you can
just begin and end a transaction around each individual operation.
Doing away with the variable (at least for autocommit mode) would also
set up for cleaner implementation of the proposed 'Transaction' attrib.
> 2. Performance degrades, because a new transaction is created for each
> new statement handle.
Well, that is up to the user to decide -- what do you think autocommit means,
after all?
the only circumstance where i could imagine doing it is if you had a special
transaction to be used for readonly operations, that had its isolation parameters
set down low, so that it could be re-used again and again without creating a
huge conflict window.
that would then correspond to the situation that some rdbms's offer but ib
does not, of performing selects "outside" of any transaction context.
the effect would be the same -- the selects would be in a transaction, but
one in which its parameters have trimmed away all the isolation.
> > 2. Not use the _commit() function at all, as it stands.
> > 3. For do(), it should create a new transaction right before, and commit it right after.
>
> 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.
>
> > Using the default IBPerl transaction should be fine.
> > 4. For execute() of a non-select, it should do the same thing as do().
> > 5. For execute() of a select (or select-like operation), it should create a new read-only
> > transaction right before the underlying execute. It should then commit that transaction
> > in fetch() and finish() (being careful to do the right thing if fetch calls finish).
>
> execute() must be run on an active transaction.
that is what happens in my above pseudo-code.
i might note that the above is not just my intellectual analysis, this is based on looking at
how other rdbms dbd drivers work.
>
> > As an enhancement request, I would suggest that prepare() and do() take an optional
> > Transaction key in the $attribs, which is a IBPerl::Transaction.
> > If that exists, it is used instead of one automatically created.
> > This is true whether AutoCommit is set or not.
>
> This looks like a great idea! This way users can gain greater control on
> the transaction isolation level.
> But this means that we must keep another transaction handle within
> imp_sth, to let the _default_ transaction handle stored within imp_dbh
> untouched. But this structure looks strange. Any idea?
the strange part to me is the default_transaction_handle, at least for autocommit.
it seems perfectly expected that a statement should have its own transaction handle.
after all, that is how the underlying api works from the rdbms vendor.
-mda
|
|
From: Edwin P. <ed....@co...> - 2000-07-28 15:17:48
|
"Mark D. Anderson" wrote: > > InterBase.pm - Today > -------------------- > InterBase.pm takes the approach of always having exactly one current open > transaction for each connection, stored in ib_trans_handle, regardless > of AutoCommit. It has a _commit() function which does an underlying commit, > then creates a new transaction for the connection, so that it always has one. > > If AutoCommit is off, it exposes commit() and rollback() functions. > It begins a transaction at connect, and does a rollback at disconnect. > > If AutoCommit is on, it disables explicit commit() or rollback(). > It automatically commits the previous transaction and starts a new > one after each do(). So far so good; but for execute(), when AutoCommit: > > 1. It does a _commit *before* the operation. This should just have the > effect of throwing away an unused transaction, because in autocommit mode > there should never be pending operations in an open transaction. Except > that isn't true because of 3 and 4.... > > 2. Then it does the underlying execute. > > 3. Then it does another _commit(), but only if the operation is a non-select. > > 4. When later a fetch() is complete or finish() is called, it doesn't do a _commit. > > This has the effect that for select operations, the client gets a very long-running > transaction, because it is never committed when it is done. This situation is > made worse because of IBPerl behavior which defaults all transactions to be mode write. > You have raised important points regarding transaction implementation. > The Fixes > --------- > IBPerl should implement transaction parameters. I have sent some starter code > directly to Bill Karwin. > > InterBase.pm needs some restructuring. > > When AutoCommit is off, it appears its current behavior is basically correct, though I > know if I was using it I would want direct access to transaction creation > (if I care enough about transactions to turn off AutoCommit, then I care > enough to stipulate their parameters). See end of this section for a suggestion. > Also it appears to me from visual inspection only that rollback() is not creating > a new transaction, and it should if it is to be consistent with the rest of the code. > > When AutoCommit is on, it appears its current behavior is incorrect. > IMHO it should: > 1. Not maintain an always active transaction. I'm afraid that we can't do this: 1. ANSI/ISO standard for SQL states that a database connection is always _in_ active transaction. 2. Performance degrades, because a new transaction is created for each new statement handle. > 2. Not use the _commit() function at all, as it stands. > 3. For do(), it should create a new transaction right before, and commit it right after. 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(). > Using the default IBPerl transaction should be fine. > 4. For execute() of a non-select, it should do the same thing as do(). > 5. For execute() of a select (or select-like operation), it should create a new read-only > transaction right before the underlying execute. It should then commit that transaction > in fetch() and finish() (being careful to do the right thing if fetch calls finish). execute() must be run on an active transaction. > As an enhancement request, I would suggest that prepare() and do() take an optional > Transaction key in the $attribs, which is a IBPerl::Transaction. > If that exists, it is used instead of one automatically created. > This is true whether AutoCommit is set or not. This looks like a great idea! This way users can gain greater control on the transaction isolation level. But this means that we must keep another transaction handle within imp_sth, to let the _default_ transaction handle stored within imp_dbh untouched. But this structure looks strange. Any idea? > That transaction is automatically committed if AutoCommit, and explicitly if not. > Also, commit() and rollback() should take an attribs argument as well. Rgds, Edwin. |
2 messages has been excluded from this view by a project administrator.
