Qt-interest Archive, December 2007
QSqlDatabase: Nested transactions ?
Message 1 in thread
Is it safe to execute nested transactions via something like:
QSqlDatabase db = QSqlDatabase::database(connection_name);
db.transaction();
db.transaction();
db.commit();
db.commit();
I can see nothing that talks about this in the Qt docs.
--
[ signature omitted ]
Message 2 in thread
yes you can't do that but there is a suggestion in the task tracker
see :
http://trolltech.com/developer/task-tracker/index_html?method=entry&id=180768
> Is it safe to execute nested transactions via something like:
>
> QSqlDatabase db = QSqlDatabase::database(connection_name);
> db.transaction();
> db.transaction();
>
> db.commit();
> db.commit();
>
> I can see nothing that talks about this in the Qt docs.
>
> --
> Regards
--
[ signature omitted ]
Message 3 in thread
le roy arnaud wrote:
> yes you can't do that but there is a suggestion in the task tracker
>
> see :
> http://trolltech.com/developer/task-tracker/index_html?method=entry&id=180768
That talks about "multiple transactions" - it's not clear to me
that that is the same thing as "nested transactions".
>> Is it safe to execute nested transactions via something like:
>>
>> QSqlDatabase db = QSqlDatabase::database(connection_name);
>> db.transaction();
>> db.transaction();
>>
>> db.commit();
>> db.commit();
>>
>> I can see nothing that talks about this in the Qt docs.
BTW, I ought to expand on this - MySQL, for example, doesn't support
nested transactions, so what I was looking for in the code above is
that all subsequent transactions() calls after the first would be
ignored until the nest was closed by the appropriate number of
commit()'s - the behaviour with other drivers may have to be
different, of course.
--
[ signature omitted ]
Message 4 in thread
Stephen Collyer wrote:
> BTW, I ought to expand on this - MySQL, for example, doesn't support
> nested transactions, so what I was looking for in the code above is
> that all subsequent transactions() calls after the first would be
> ignored until the nest was closed by the appropriate number of
> commit()'s - the behaviour with other drivers may have to be
> different, of course.
Having thought about this a little more, it seems easy
to fix, in my case at least.
1. Db connections cannot be shared between threads
2. I have a wrapper class, RunQuery, around a bunch of query
generation methods.
This means that I can add transaction() and commit()
methods to RunQuery that do:
bool in_transaction;
RunQuery::transaction()
{
if (!in_transaction)
{
db.transaction();
}
}
RunQuery::commit()
{
if (in_transaction)
{
db.commit();
}
}
Given that it's all single-threaded code, we don't
have to worry about keeping stacks of transactions and commits that
we must reconcile - we're either doing a traction or we're not.
--
[ signature omitted ]
Message 5 in thread
Stephen,
What is exactly what you want to do? Do you want to have separate methods
that execute sql statements/procedures as part of one transaction? Then my
suggestion is to pass to these methods the QSqlDatabase object you want to
run them against and there is no need for nested transactions. You would
need to have a top method that controls the transaction and then this method
calls the other methods and it passes the QSqlDatabase object. You could
design, for instance, a thin wrapper that runs a list of sql commands inside
one transaction.
As you mentioned in your email below, not all the databases support nested
transactions. Oracle, for instance, doesn't support nested transactions and
you have to be careful because as soon as you call commit, all the
statements executed until your commit will be committed in the database.
Please also note that Oracle doesn't support the begin transaction method.
With oracle you always have an implicit transaction. As soon as you commit
or rollback you have a new transaction until you commit or rollback (or you
close the transaction) and so on (provided that auto commit is false).
About sharing connections between threads I think it is always safer to run
one query at the time on one connection.
One thing I have to say and that is I am not very proud of the database
support in Qt. I think they just gave enough support to be usable, but if
you try more complicated stuff it is not there yet, and this is especially
true with Oracle. There is no native support for cursors, collections and so
on. Java and jdbc are much more advanced for that matter.
So far, I haven't seen a good and complete c++ wrapper library for database
access that ties with the UI and matches ado.net or the borland delphi
database library (they have been designed by the same individual, I believe,
Anders Hejlsberg). I don't know why Trolltech doesn't invest more time and
brain into the sql module. It almost looks as if there isn't much qt
database development going on, or maybe people use only the basic sql
statements. Maybe there isn't money to be made from this. I really don't
know and I am fairly dissapointed.
B.C.
"Stephen Collyer" <scollyer@xxxxxxxxxxxxxxxx> wrote in message
news:4766A76C.80400@xxxxxxxxxxxxxxxxxxx
> Stephen Collyer wrote:
>
> > BTW, I ought to expand on this - MySQL, for example, doesn't support
> > nested transactions, so what I was looking for in the code above is
> > that all subsequent transactions() calls after the first would be
> > ignored until the nest was closed by the appropriate number of
> > commit()'s - the behaviour with other drivers may have to be
> > different, of course.
>
> Having thought about this a little more, it seems easy
> to fix, in my case at least.
>
> 1. Db connections cannot be shared between threads
> 2. I have a wrapper class, RunQuery, around a bunch of query
> generation methods.
>
> This means that I can add transaction() and commit()
> methods to RunQuery that do:
>
> bool in_transaction;
>
> RunQuery::transaction()
> {
> if (!in_transaction)
> {
> db.transaction();
> }
> }
>
> RunQuery::commit()
> {
> if (in_transaction)
> {
> db.commit();
> }
> }
>
> Given that it's all single-threaded code, we don't
> have to worry about keeping stacks of transactions and commits that
> we must reconcile - we're either doing a traction or we're not.
>
> --
> Regards
>
> Steve Collyer
> Netspinner Ltd
>
> --
> To unsubscribe - send a mail to qt-interest-request@xxxxxxxxxxxxx with
"unsubscribe" in the subject or the body.
> List archive and information: http://lists.trolltech.com/qt-interest/
--
[ signature omitted ]
