Yes, I'm afraid so, if you want to use Class::DBI. You should consider changing your design to use a single database.

Which kinda sucks if you have large blob fields as they can impact performance if they are in the same db as your other fields -- but then again didn't you toss your right to performance when you useed Class::DBI? =)


-Waswas

Class::DBI follows all of the DBI best practices for performance. Separating fields into different databases for performance reasons is a very specialized task that no general tool is ever going to do for you. In general, Class::DBI can be helpful to performance because it implements things like lazy-loading that most people don't bother to do when they write their own database access objects.

Thanks perrin.

One other off-topic question: if we refactor the DBs into one monster DB over time, what are the downsides? It is harder for our power users to find tables, maybe, if everything is in one big bag. But beyond that, are there any CDBI downsides? Any database performance issues? Backup issues? The earlier comment about BLOBs was instructive -- while we're not using them, the comment made us realize that refactoring many small INNODBs into one large INNODB could have other consequences of which we're not aware......

(apologies for this OT post, but it continues the thread, and we're curious.)

I'm not aware of any downsides to having all of your tables in one database, especially if you are already using them together.

If multiple databases in MySQL are similar to multiple schemas in Oracle (i.e. you can just access tables by saying db_name.table_name), you may be able to convince Class::DBI to use that syntax. Try setting the table name in one of your classes to that fully-qualified syntax and see what happens.

Handling the rollback at the app level, not the DB level, by rolling back each handle if anything fails.

It calls a function AllTables::handles that returns all handles to every CDBI class (this isn't part of CDBI, but it is easy to cache the handles when the classes are defined) -- rolling back every object is probably somewhat redundant and overkill, as handles are unique by database, not by table, but I think that redundancy does no harm.

sub do_transaction {
    my ($code) = @_;
    &transactions_on;
    eval { $code->() };
    &rollback_and_die if $@;
    &commit;
    &transactions_off;
}

sub rollback_and_die {
    my $commit_error = $@; # save it as rollback could fail
    eval { $_->rollback foreach (AllTables::handles); };
    &transactions_off;    # just to be safe
    die $commit_error;    # and fatal death
}

sub commit {
    eval { $_->commit foreach (AllTables::handles); };
    &rollback_and_die if $@;    # commit failed, so bail
}

sub transactions_on {
    $_->{AutoCommit} = 0 foreach (AllTables::handles);
}

sub transactions_off {
    $_->{AutoCommit} = 1 foreach (AllTables::handles);
}
[download]

You should really test this under heavy load, with lots of concurrent users, and killing the client apps at random intervals to make sure that your code really works, and that all transactions that should be committed or rolled back together are performed correctly.

Michael

Can you do transactions spanning multiple connections? I don't know of any database server that would allow you to do that.

Unless this is possible, I think you erred by organizing in multiple databases.

Liz

Class::DBI may not be able to do this (and maybe MySQL can't handle it) - but with Sybase I can have a single transaction span multiple databases on a single connection. You simply have to fully qualify the name of the tables with the database:

begin tran
update db1.dbo.foo set bar = 1 where baz=2
delete db2.dbo.bar where baz=2
commit tran
[download]

(where "dbo" above is the table owner - in this case the "database owner").

Michael

MySQL can handle multiple database transactions in a single connection as well.

The problem is that Class::DBI is forcing multiple connections, thus preventing the transaction from working properly.

Can you do transactions spanning multiple connections?

No, clearly not.

I don't know of any database server that would allow you to do that.

Agreed.

But if we could get all the DBI handles out of Ima::DBI, we're thinking we could roll each handle back if any piece failed...

Not sure, though, how to get at all the handles.