You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@apr.apache.org by Bojan Smojver <bo...@rexursive.com> on 2006/05/02 06:45:38 UTC
Transaction modes, explicit rollbacks etc.
So, we know by now that some databases will fail the whole transaction
if any kind of error occurs, while some others won't. We also know
that sometimes one option is the desired behaviour, while at other
times the other option is what the caller wants - regardless of the
underlying database. So, given that APR DBD is supposed to be database
neutral, here is another solution for the same problem.
#define APR_DBD_TRANSACTION_COMMIT 0x00 /**< commit the transaction */
#define APR_DBD_TRANSACTION_ROLLBACK 0x01 /**< rollback the
transaction */
#define APR_DBD_TRANSACTION_IGNORE_ERRORS 0x02 /**< ignore error conditions */
We then have our already known functions:
apr_dbd_transaction_mode_get()
apr_dbd_transaction_mode_set()
So, we can call (once only!):
apr_dbd_transaction_mode_set(driver, trans,
APR_DBD_TRANSACTION_COMMIT|
APR_DBD_TRANSACTION_IGNORE_ERRORS);
Then, inside the query/select functions we leave trans->errnum at zero
in case any error occurs, but return the status to the caller. If the
caller is intersted in the status, he/she can do something about it
(such as call mode set with ROLLBACK and then call end, to rollback).
Or, the caller can just continue with the next query/select. With
IGNORE_ERRORS set, at no point will the transaction be in a bad state
(i.e. this is how SQLite/Oracle/MySQL work). After all that, COMMIT is
executed and whatever is good gets committed (so this is effectively
COMMIT_ON_SUCCESS, but without the need to keep "recovering" the error
status).
In case the IGNORE_ERRORS flag isn't set, the logic reverts to the
current behaviour (i.e. the way PGSQL does things). Any error triggers
a transaction error and the whole transaction will be rolled back for
sure.
Here is the part we can now do with IGNORE_ERRORS that we could not do
before - we can make PGSQL behave like other databases if we so wish.
By setting IGNORE_ERRORS, we can insert SAVEPOINTS before every
query/select inside PGSQL driver automatically (i.e. the caller
doesn't have to do it). If an error occurs withing query/select, we
simply roll back to the previous savepoint, keep trans->errnum always
at zero and return the error code of the actual query. The caller can
then decide what should be done with the whole thing. This brings us
in line with transactional behaviour other databases.
In summary, we can pick the behaviour we want and users get
savepoints/error recovery for free with PGSQL if they so wish.
Let me know what you think...
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
Quoting Bojan Smojver <bo...@rexursive.com>:
> Darn! I was hoping you won't say that. Now I have to do all those
> savepoints... ;-)
Compile this one if you can! ;-)
Seriously, just some more ground work along the same lines...
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
Quoting Bojan Smojver <bo...@rexursive.com>:
> We can then handle failures by having a wait loop inside
> transaction_start(), just like we do with select/query.
Actually, it is dbd_sqlite3_query() that's going to do that for us, so
all we need is just BEGIN IMMEDIATE.
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
Quoting Bojan Smojver <bo...@rexursive.com>:
> Maybe we should introduce APR_DBD_TRANSACTION_IMMEDIATE/EXCLUSIVE modes
> to tell SQLite3 what we really want? This would have no effect on other
> drivers, of course.
Actually, after reading SQLite3 documentation a bit more, probably the
best thing to do is to just BEGIN IMMEDIATE (most people want to
update the database when using transactions). This may lead to writer
starvation in extreme concurrency situations, but at least it avoids
the deadlocks.
We can then handle failures by having a wait loop inside
transaction_start(), just like we do with select/query.
If nobody objects to this in the next few days, I'm going to commit
changes to this effect.
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
On Thu, 2006-06-22 at 14:31 -0700, Rick Keiner wrote:
> After some problems I was having with "cannot start a transaction
> within a transaction" problem with SQLite3, I came across this and I
> think a check should go in the transaction handling for busy
> conditions in the end transaction.
>
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg15985.html
Finally got some time to experiment with this. Under high concurrency of
transactions (20 threads, both read and update transactions from all
threads), it is much better to have BEGIN IMMEDIATE or BEGIN EXCLUSIVE
at the start of the transaction and then handle transaction failures by
the caller. Starting a transaction blindly and then waiting around in
query/select (basically in a deadlock) was almost 20% performance loss
in my tests.
Maybe we should introduce APR_DBD_TRANSACTION_IMMEDIATE/EXCLUSIVE modes
to tell SQLite3 what we really want? This would have no effect on other
drivers, of course.
--
Bojan
SQLite3 transaction deadlock [WAS] Re: Transaction modes, explicit
rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
On Thu, 2006-06-22 at 14:31 -0700, Rick Keiner wrote:
> After some problems I was having with "cannot start a transaction
> within a transaction" problem with SQLite3, I came across this and I
> think a check should go in the transaction handling for busy
> conditions in the end transaction.
>
> http://www.mail-archive.com/sqlite-users@sqlite.org/msg15985.html
The way I'm reading this is that if we're inside a transaction and we
get busy, we should just return errors and don't waste time waiting.
Then (because we're in error on the transaction level), the code of
end_transaction will rollback for us. Right?
ATM, we're retrying on busy even if we're inside the transaction, which
then according to the above e-mail isn't going to work no matter what we
do.
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by Rick Keiner <ri...@yahoo.com>.
Hi,
After some problems I was having with "cannot start a transaction within a transaction" problem with SQLite3, I came across this and I think a check should go in the transaction handling for busy conditions in the end transaction.
http://www.mail-archive.com/sqlite-users@sqlite.org/msg15985.html
Regards,
Rick Keiner
Chris Darroch <ch...@pearsoncmg.com> wrote: Bojan Smojver wrote:
>> I would stick to the current meaning, mostly because of this:
>>
>> ---------------------------
>> if (!trans)
>> return APR_DBD_TRANSACTION_COMMIT;
>> ---------------------------
>>
>> In essence, the function returns "the mode in force after the call"
>> (actually, I should change the documentation to say that explicitly).
>
> Chris, are you OK with this explanation? Can I go ahead and commit this?
Yes, by all means, commit away! I'm also rolling this big patch
into own production stuff. Much appreciated!
Chris.
--
GPG Key ID: 366A375B
GPG Key Fingerprint: 485E 5041 17E1 E2BB C263 E4DE C8E3 FA36 366A 375B
Re: Transaction modes, explicit rollbacks etc.
Posted by Chris Darroch <ch...@pearsoncmg.com>.
Bojan Smojver wrote:
>> I would stick to the current meaning, mostly because of this:
>>
>> ---------------------------
>> if (!trans)
>> return APR_DBD_TRANSACTION_COMMIT;
>> ---------------------------
>>
>> In essence, the function returns "the mode in force after the call"
>> (actually, I should change the documentation to say that explicitly).
>
> Chris, are you OK with this explanation? Can I go ahead and commit this?
Yes, by all means, commit away! I'm also rolling this big patch
into own production stuff. Much appreciated!
Chris.
--
GPG Key ID: 366A375B
GPG Key Fingerprint: 485E 5041 17E1 E2BB C263 E4DE C8E3 FA36 366A 375B
Re: Transaction modes, explicit rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
Quoting Bojan Smojver <bo...@rexursive.com>:
> I would stick to the current meaning, mostly because of this:
>
> ---------------------------
> if (!trans)
> return APR_DBD_TRANSACTION_COMMIT;
> ---------------------------
>
> In essence, the function returns "the mode in force after the call"
> (actually, I should change the documentation to say that explicitly).
Chris, are you OK with this explanation? Can I go ahead and commit this?
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
Quoting Chris Darroch <ch...@pearsoncmg.com>:
> I can confirm the following, as tested with all four of the
> p[v]query|select() functions (since the Oracle driver treats them
> each separately):
>
> Database | Explicit ROLLBACK | Ignore TXN errors
> ------------------------------------------------
> oracle | Yes | Yes
> ------------------------------------------------
Thanks!
> One question, though ... should apr_dbd_transaction_mode_set()
> perhaps return the old mode instead of the new one? I suppose
> I'm thinking of something like apr_atomic_casptr(). Dunno which
> behaviour is more common; I can't find many other examples in APR.
I would stick to the current meaning, mostly because of this:
---------------------------
if (!trans)
return APR_DBD_TRANSACTION_COMMIT;
---------------------------
In essence, the function returns "the mode in force after the call"
(actually, I should change the documentation to say that explicitly).
But, if the list disagrees, I'm OK to change as well.
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by Chris Darroch <ch...@pearsoncmg.com>.
Bojan:
I can confirm the following, as tested with all four of the
p[v]query|select() functions (since the Oracle driver treats them
each separately):
Database | Explicit ROLLBACK | Ignore TXN errors
------------------------------------------------
oracle | Yes | Yes
------------------------------------------------
One question, though ... should apr_dbd_transaction_mode_set()
perhaps return the old mode instead of the new one? I suppose
I'm thinking of something like apr_atomic_casptr(). Dunno which
behaviour is more common; I can't find many other examples in APR.
Chris.
--
GPG Key ID: 366A375B
GPG Key Fingerprint: 485E 5041 17E1 E2BB C263 E4DE C8E3 FA36 366A 375B
Re: Transaction modes, explicit rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
Quoting "Philip M. Gollucci" <pg...@p6m7g8.com>:
>> A patch against the latest version of MySQL driver is also attached.
> I've got some relative good mysql foo. I can lend a hand against
> MySQL 3.23, 4.0.18, 4.0.26, 4.1.20, 5.0.21, 5.1.9-beta testing if needed.
Only those versions? ;-)
Seriously, your help is much appreciated. BTW, don't bother with 3.x
or 4.0 - the driver is designed for 4.1 and above. Probably the most
interesting test would be against 5.1.
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by "Philip M. Gollucci" <pg...@p6m7g8.com>.
>A patch against the latest version of MySQL driver is also
> attached.
I've got some relative good mysql foo. I can lend a hand against
MySQL 3.23, 4.0.18, 4.0.26, 4.1.20, 5.0.21, 5.1.9-beta testing if needed.
--
------------------------------------------------------------------------
Philip M. Gollucci (pgollucci@p6m7g8.com) 323.219.4708
Consultant / http://p6m7g8.net/Resume/resume.shtml
Senior Software Engineer - TicketMaster - http://ticketmaster.com
1024D/A79997FA F357 0FDD 2301 6296 690F 6A47 D55A 7172 A799 97F
"It takes a minute to have a crush on someone, an hour to like someone,
and a day to love someone, but it takes a lifetime to forget someone..."
Re: Transaction modes, explicit rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
Quoting Bojan Smojver <bo...@rexursive.com>:
> OK, here is the summary:
Oh, and I will adjust the transaction documentation some more before
the commit, so that users can tell what the logic is.
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
Quoting Bojan Smojver <bo...@rexursive.com>:
> Database | Explicit ROLLBACK | Ignore TXN errors
> ------------------------------------------------
> oracle | Not tested | Not tested
> ------------------------------------------------
Above also appears to be Yes and Yes (Oracle 10g). Someone with better
knowledge of Oracle should double check.
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
Quoting Bojan Smojver <bo...@rexursive.com>:
> Database | Explicit ROLLBACK | Ignore TXN errors
> ------------------------------------------------
> sqlite2 | Not tested | Not tested
> ------------------------------------------------
Make the above Yes and Yes.
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
Quoting Rick Keiner <ri...@yahoo.com>:
> What's the status of the API change? This would bump the rev to 1.3,
> correct? Any idea what the time frame is on this?
OK, here is the summary:
Database | Explicit ROLLBACK | Ignore TXN errors
------------------------------------------------
pgsql | Yes | Yes
------------------------------------------------
mysql | Yes (InnoDB) | Yes
------------------------------------------------
sqlite3 | Yes | Yes
------------------------------------------------
sqlite2 | Not tested | Not tested
------------------------------------------------
oracle | Not tested | Not tested
------------------------------------------------
I had to make some changes to the PostgreSQL patch - there were subtle
bugs related to result sets. So, I'm attaching a new patch for
reference. A patch against the latest version of MySQL driver is also
attached.
I'm not forseeing problems with SQLite2 - the code is much the same as
for SQLite3, so it should work. I just don't have the environment
handy to test.
As for Oracle, this needs to be tested by folks that understand both
the driver and the backend properly. Skeleton code is attached, feel
free to have a go at it.
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
Quoting Rick Keiner <ri...@yahoo.com>:
> Any idea what the time frame is on this?
Basic tests with PostgreSQL query/pquery under
APR_DBD_TRANSACTION_IGNORE_ERRORS show that transactions continue (due
to explicit savepoints inserted by the driver) even in the face of
errors. Explicit rollback (i.e. APR_DBD_TRANSACTION_ROLLBACK) also
appears to work with PostgreSQL.
I will test PostgreSQL some more, as well as SQLite3 and MySQL.
Unfortunately, others will have to test Oracle (don't have the
environment handy), preferably before the commit.
She's breathing captain, she's breathing... :-)
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
On Thu, 2006-06-01 at 07:27 -0700, Rick Keiner wrote:
> What's the status of the API change? This would bump the rev to 1.3,
> correct? Any idea what the time frame is on this?
I got a bit sidetracked with the MySQL driver stuff, but I'm testing the
patch in my setup. I'm planning to test the whole thing a little bit
better and then commit to the trunk (i.e. this cannot go to 1.2.x, due
to binary compatibility requirements). I would like to tell you that
this is going to be tomorrow, but this depends on my work and other
commitments.
The code I'm testing (minus the MySQL required driver change, which I
still have to write) is attached. The only thing I can say about it for
now is that it compiles on my system.
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
Quoting Rick Keiner <ri...@yahoo.com>:
> What's the status of the API change?
Here is the required MySQL driver change for the new TXN API to work.
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by Rick Keiner <ri...@yahoo.com>.
What's the status of the API change? This would bump the rev to 1.3, correct? Any idea what the time frame is on this?
Thanks,
Rick Keiner
Bojan Smojver <bo...@rexursive.com> wrote: On Tue, 2006-05-02 at 10:15 -0400, Chris Darroch wrote:
> This is sounding good to me!
Darn! I was hoping you won't say that. Now I have to do all those
savepoints... ;-)
> I've promised I'll hustle up on
> my httpd work so I apologize for my relative absence of help on
> this thread, but by all means ping me privately if you want a hand.
No worries.
I'll wait for other folks to share their views before I do anything that
even resembles a working patch. I think I should be able to get myself
out of trouble with open source databases (getting cocky, eh? :-), but
Oracle driver really scares me, as things appear to be a little bit more
complicated there.
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
On Tue, 2006-05-02 at 10:15 -0400, Chris Darroch wrote:
> This is sounding good to me!
Darn! I was hoping you won't say that. Now I have to do all those
savepoints... ;-)
> I've promised I'll hustle up on
> my httpd work so I apologize for my relative absence of help on
> this thread, but by all means ping me privately if you want a hand.
No worries.
I'll wait for other folks to share their views before I do anything that
even resembles a working patch. I think I should be able to get myself
out of trouble with open source databases (getting cocky, eh? :-), but
Oracle driver really scares me, as things appear to be a little bit more
complicated there.
--
Bojan
Re: Transaction modes, explicit rollbacks etc.
Posted by Chris Darroch <ch...@pearsoncmg.com>.
Bojan Smojver wrote:
> Actually, Oracle will commit even if there were errors. So, the
> situation is that for PostgreSQL, resetting the transaction error code
> is meaningless - the whole transaction will be rolled back eventually,
> unless explicit savepoints are used. Other databases will happily
> ignore the errors and commit whatever remains.
I can confirm this, FWIW.
> Here is the part we can now do with IGNORE_ERRORS that we could not do
> before - we can make PGSQL behave like other databases if we so wish.
> By setting IGNORE_ERRORS, we can insert SAVEPOINTS before every
> query/select inside PGSQL driver automatically (i.e. the caller
> doesn't have to do it). If an error occurs withing query/select, we
> simply roll back to the previous savepoint, keep trans->errnum always
> at zero and return the error code of the actual query. The caller can
> then decide what should be done with the whole thing. This brings us
> in line with transactional behaviour other databases.
>
> In summary, we can pick the behaviour we want and users get
> savepoints/error recovery for free with PGSQL if they so wish.
>
> Let me know what you think...
This is sounding good to me! I've promised I'll hustle up on
my httpd work so I apologize for my relative absence of help on
this thread, but by all means ping me privately if you want a hand.
Your focus on this set of problems is much appreciated, believe me.
Chris.
--
GPG Key ID: 366A375B
GPG Key Fingerprint: 485E 5041 17E1 E2BB C263 E4DE C8E3 FA36 366A 375B
Re: Transaction modes, explicit rollbacks etc.
Posted by Bojan Smojver <bo...@rexursive.com>.
Quoting Bojan Smojver <bo...@rexursive.com>:
> By setting IGNORE_ERRORS, we can insert SAVEPOINTS before every
> query/select inside PGSQL driver automatically (i.e. the caller
> doesn't have to do it).
Something like this. Note that this is just to show the concept - I'm
sure none of it would actually work, especially the Oracle bit and
savepoints in PGSQL (especially the unimplemented ones in
pquery/pselect :-). Anyhow, it should be sufficient to see the
proposal in terms of code.
--
Bojan