On Wed, 2005-05-18 at 14:18 +0200, Gerhard Haering wrote:
> In pysqlite, I need to keep track of the transaction state (in
> transaction/not in transaction). To handle ON CONFLICT ROLLBACK in
> pysqlite correctly, I need to be able to query the transaction state.
>
> As I see it, that's currently not possible. Am I mistaken?
>
> Otherwise, could you please implement a fast way to get the transaction
> state, like an additional API call?
>
Determining whether or not SQLite is in a transaction is more
difficult than it sounds. Consider a case where you have a
main database and an attached database:
ATTACH DATABASE 'aux.db' AS aux;
Then you do a statement like the following that contains
a user-defined function:
INSERT INTO table1 SELECT user_function(x) FROM table2;
Because this is an INSERT statement, SQLite automatically
starts a transaction on the main database but not on the
aux database. It would have also started a transaction on
the aux database had the aux database been used, but aux
is not being used so it leaves it alone. Suppose the
implementation of user_function() calls the routine that
asks whether or not SQLite is within a transaction. On
database is, the other is not. What does the API return?
Let's dig further. Issuing a BEGIN statement does not
start a transaction by itself. BEGIN just signals to
SQLite to not do an autocommit. The transaction does not
start until you do some other statement that requires the
transaction. So we have:
-- not in a transaction
BEGIN;
-- not in a transaction
INSERT INTO table1 VALUES(1);
-- in a transaction
COMMIT;
-- not in a transaction
On the other hand, BEGIN IMMEDIATE starts the transaction
right away rather than waiting for the first statement that
needs the transaction.
So the question of whether or not SQLite is within a transaction
is not as clear-cut as one would think at first.
Question: Might it be that you really want to know if
SQLite is in auto-commit mode or not? That is a very different
thing and is a much easier question to answer.
--
D. Richard Hipp <[EMAIL PROTECTED]>
