[yunqa.de] Re: How to tell a transaction is active?
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Thu, 31 Jul 2008 15:28:58 +0200
Clyde England wrote:
>I am using TDISQLite3Database to update my sqlite database and want to be able
>to determine if we are currently in a transaction.
SQLite has no dedicated functionality to determine if a database is currently
in a transaction. The only way to do so is to use
sqlite3_get_autocommit()
which returns non-zero or zero if the given database connection is or is not in
autocommit mode, respectively. Autocommit mode is on by default. Autocommit
mode is disabled by a BEGIN statement. Autocommit mode is re-enabled by a
COMMIT or ROLLBACK.
In Pascal this translates to:
TransactionActive := sqlite3_get_autocommit(db) = 0;
>TDISQLite3Database has a property called "InTransaction" (BTW, this property
>is shown in the help but not documented ), so I thought this would do the job.
>
>However, it doesn't seem to work as expected. Take the following simple code:
>
> dbs.execute('begin');
> dbs.execute('update logs set tval = 1');
> if dbs.intransaction then ShowMessage('we are in a transaction');
>
>I would expect dbs.InTransaction to now be true and we should get the message
>box pop up saying "we are in a transaction"
TDISQLite3Database simulates nested transactions (currently unsupported by
SQLite) with the help of a transaction counter. However, this only works with
the TDISQLite3Database's native methods StartTransaction, Commit, and Rollback.
Here is an example:
{ Check transaction counter and start new transaction if 0,
then increment transaction counter. }
DISQLite3Database.StartTransaction;
{ This assert now passes fine. }
Assert (DISQLite3Database.InTransaction);
{ Decrement transaction counter and commit transaction if 0. }
DISQLite3Database.Commit;
You can call StartTransaction multiple times, but they must be matched by just
as many calls to Commit and/or Rollback. However, only the first transaction
will have any true transaction effect. Subsequent transaction will become
effective only when SQLite will support nested transaction sometime in the
future.
>However, dbs.InTransaction at this point in the code is false, and hence we
>don't get the message.
You will, if you use TDISQLite3Database.StartTransaction,
TDISQLite3Database.Commit, and TDISQLite3Database.Rollback. Since they each
modify and depend on the internal transaction counter, do not mix them with
direct SQL.
Otherwise use sqlite3_get_autocommit(). But please note that if another thread
changes the autocommit status of the database connection while this routine is
running, then the return value is undefined.
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
