[RDBO] Still fighting with transactions
Hello, I thought I understood how to organize several write operations within a transaction but it still doesn't work the way I do it. Here is what I have so far: In my base class: sub init_db { my $self = shift; my $db; if (not $self-my_db) { $self-my_db( MyApp::DB-new() ); } return $self-my_db; } { # closure my $db; sub my_db { my $self = shift; if(@_) { $db = $_[0]; } return $db; } } The idea is that I don't have to write 'db = $db' in every call. In my script (run through mod_perl if it makes a difference): my $db = MyApp::DB-new; $self-my_db($db); # a new db for a new transaction $db-begin_work; # Start transaction ... create two objects and save them ... die Does it roll back?; $db-commit; But when I run this script the two records are saved to the database and not rolled back. Edit: It is even more mysterious: The saved records are only there as long as apache isn't restartet. I tried clearing the cache, calling the records in different ways, no matter they are there. After a restart they are not. It looks as if the rollback is not done at the die of the script but the 'kill' of the server ?!? I have basically two questions: - What is wrong with my code? Why doesn't it work - Are there better variants for init_db? Ideally I would like to share a db throughout a mod_perl request but not longer. This way I would just wrap every handler that writes to the database within a $db-begin_work / $db-commit and could be sure that either everything or nothing is saved. Any ideas? Thanks - Michael - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] help specifying a default datetime with SQLite and RDBO::Loader
John Siracusa writes: On 8/7/07 5:12 PM, George Hartzell wrote: If I change Rose::DB::SQLite::validate_datetime_keyword so that it'll accept 'current_timestamp' Yeah, I should do that... then the value gets inserted literally into the table. it probably also needs to be inlined (i.e., unquoted) I'd like to be able to specify the default behaviour in the schema, it'll be more robust if/when it's used outside of rdbo. A default value of now should work in all supported databases, IIRC. The default value of now does work when used in the RDBO perl module. The problem is that since I'm using RDBO::Loader, the only way to get that value in there is to use it in the SQL, where it doesn't do what is intended. Eventually I may stop generating the perl classes on the fly, but in the meantime I'd like it to do the right thing. Can you give me a hint on setting up the inlining? It looks like I'll need to change Rose::DB::Object::Metadata::Column::Timestamp::should_inline_value so that it checks (off the top of my head) ($_[1]-driver eq 'sqlite' $_[2] =~ /current_datetime/i) or something close to that. I'll have to figure out how to work it into the existing ternary test. Thanks, g. - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] help specifying a default datetime with SQLite and RDBO::Loader
On 8/14/07, George Hartzell [EMAIL PROTECTED] wrote: The default value of now does work when used in the RDBO perl module. The problem is that since I'm using RDBO::Loader, the only way to get that value in there is to use it in the SQL, where it doesn't do what is intended. Remember that the Loader also provides hooks to modify the metadata pulled from the database, both before and after class initialization. In this case, you could add a pre_init_hook that walks over all the columns and replaces any default current_datetime values on datetime columns with now. http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/Loader.pm#pre_init_hook Can you give me a hint on setting up the inlining? It looks like I'll need to change Rose::DB::Object::Metadata::Column::Timestamp::should_inline_value so that it checks (off the top of my head) ($_[1]-driver eq 'sqlite' $_[2] =~ /current_datetime/i) or something close to that. I'll have to figure out how to work it into the existing ternary test. You shouldn't need to modify should_inline_value(). Instead, add current_datetime as a valid keyword for SQLite date/time columns. To do this, alter the validate_*_keyword() method(s) in Rose::DB::SQLite. This fix will be in the next release, but I haven't don't it yet. If you do it first, feel free to post a patch :) -John - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On 8/14/07, Michael Lackhoff [EMAIL PROTECTED] wrote: my $db = MyApp::DB-new; $self-my_db($db); # a new db for a new transaction $db-begin_work; # Start transaction ... create two objects and save them ... die Does it roll back?; $db-commit; I don't see any rollback there. It's a common practice to put an automatic rollback in a cleanup handler when running mod_perl. Another approach is to catch exceptions and issue the rollback there. If you did one of those, this would work. Otherwise, I don't see how you expect it to get rolled back. Edit: It is even more mysterious: The saved records are only there as long as apache isn't restartet. I tried clearing the cache, calling the records in different ways, no matter they are there. After a restart they are not. It looks as if the rollback is not done at the die of the script but the 'kill' of the server ?!? That implies that they aren't committed either, and that you are running with a dirty read isolation level that lets you see data that hasn't been committed yet on other database connections. Either that, or you are somehow using the same connection in multiple processes, which would be very bad. - Perrin - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On 14.08.2007 16:54 Perrin Harkins wrote: $db-begin_work; # Start transaction die Does it roll back?; $db-commit; I don't see any rollback there. It's a common practice to put an Well, my understandig was that if perl dies within a transaction it (or DBI) would do a rollback, from your answer I guess this is not the case. automatic rollback in a cleanup handler when running mod_perl. do you have any pointer on how this is done (correctly)? Edit: It is even more mysterious: The saved records are only there as long as apache isn't restartet. I tried clearing the cache, calling the records in different ways, no matter they are there. After a restart they are not. It looks as if the rollback is not done at the die of the script but the 'kill' of the server ?!? That implies that they aren't committed either, and that you are running with a dirty read isolation level that lets you see data that hasn't been committed yet on other database connections. Either that, or you are somehow using the same connection in multiple processes, which would be very bad. How do I manipulate the isolation level? I thought the begin_work would set it as it should be. And to the second alternative, how do I have to setup RDBO with mod_perl to share a db (and dbh with it) for everything within one process/request but not across multiple processes? Many questions but this seems to be very essential stuff and I really want to learn how to get it right. So, if you have any pointers or even example code this would be more than welcome... Many Thanks - Michael - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On 8/14/07, Michael Lackhoff [EMAIL PROTECTED] wrote: Well, my understandig was that if perl dies within a transaction it (or DBI) would do a rollback, from your answer I guess this is not the case. No. Your database will do it if you cut the connection and there is uncommitted work, but perl or DBI won't do a rollback just because you called die. Remember, in a persistent environment like mod_perl, die doesn't stop the perl interpreter or cut the database connection. automatic rollback in a cleanup handler when running mod_perl. do you have any pointer on how this is done (correctly)? $r-push_handlers(PerlCleanupHandler = sub { MyApp::DB-rollback(); }); I don't know what the proper way to get a database handle is in your setup, but all you need to do is get one and call rollback. How do I manipulate the isolation level? It depends on your database. What database are you using? I thought the begin_work would set it as it should be. It should start a transaction, but isolation level is a higher-level concept. http://en.wikipedia.org/wiki/Isolation_level And to the second alternative, how do I have to setup RDBO with mod_perl to share a db (and dbh with it) for everything within one process/request but not across multiple processes? Unless you open a handle during server startup and try to keep it, it will not be shared between processes. There could be something going on with your closure stuff there if you do that during startup. The more likely answer though is that you just have your database in a READ UNCOMMITTED (aka dirty read) isolation level and the handles are not shared. I will leave it for someone else to comment on the way you're managing your database handles because I don't have enough first-hand experience with what you're doing there to give you good advice. - Perrin - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On 14.08.2007 17:50 Perrin Harkins wrote: do you have any pointer on how this is done (correctly)? $r-push_handlers(PerlCleanupHandler = sub { MyApp::DB-rollback(); }); Thanks! That's exactly what I needed. I found already that there is a PerlCleanupHandler directive in httpd.conf but your code shows how to do it from within only those requests that need it, great! Only question: Is it really possible to call rollback as a class method or do I have to call it on the specific object that started the transaction? How do I manipulate the isolation level? It depends on your database. What database are you using? SQLite, but if at all possible I would set it in some generic way with RDBO because I might well change the database in the future. Unless you open a handle during server startup and try to keep it, it will not be shared between processes. There could be something going on with your closure stuff there if you do that during startup. The more likely answer though is that you just have your database in a READ UNCOMMITTED (aka dirty read) isolation level and the handles are not shared. I will leave it for someone else to comment on the way you're managing your database handles because I don't have enough first-hand experience with what you're doing there to give you good advice. Perrin, your answer was really great help, thanks for taking the time! If now some kind soul would share his/her init_db() that'll be my day ;-) - Michael (already trying for two weekends to get this right) - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On 8/14/07, Michael Lackhoff [EMAIL PROTECTED] wrote: Only question: Is it really possible to call rollback as a class method or do I have to call it on the specific object that started the transaction? You have to call it on the database handle that started the transaction. If all of your objects (within the same request) use the same database handle, there is no difference. A rollback affects everything on that database connection. How do I manipulate the isolation level? It depends on your database. What database are you using? SQLite, but if at all possible I would set it in some generic way with RDBO because I might well change the database in the future. Since the syntax can vary a bit from one database to another, there isn't a truly generic way to do it. I don't use SQLite, but there's some information on how to set this on the pragma page: http://www.sqlite.org/pragma.html - Perrin - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On 8/14/07, Michael Lackhoff [EMAIL PROTECTED] wrote: And to the second alternative, how do I have to setup RDBO with mod_perl to share a db (and dbh with it) for everything within one process/request but not across multiple processes? The simplest way to do this is to leave everything as per the defaults, and just use Apache::DBI. The default init_db() method looks like this: sub init_db { Rose::DB-new() } If you just use Apache::DBI in your mod_perl process, then every Rose::DB-derived object will get its own Rose::DB db object, but all of those db objects within a single Apache child will share a single DBI $dbh connection. If you have your own Rose::DB subclass (as you probably should) then just change init_db() to this in your common base casee: sub init_db { My::DB-new() } Some more info here: http://www.mail-archive.com/rose-db-object@lists.sourceforge.net/msg01139.html -John - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On 8/14/07, John Siracusa [EMAIL PROTECTED] wrote: The simplest way to do this is to leave everything as per the defaults, and just use Apache::DBI. Note that Apache::DBI also does the automatic rollback for you, but only if you have AutoCommit off when you connect. If you connect with AutoCommit on, you have to handle the rollback yourself. - Perrin - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On 8/14/07, Perrin Harkins [EMAIL PROTECTED] wrote: Note that Apache::DBI also does the automatic rollback for you, but only if you have AutoCommit off when you connect. If you connect with AutoCommit on, you have to handle the rollback yourself. Doesn't Apache::DBI just care that $dbh-{AutoCommit} is false at the time it checks in the cleanup handler? From the cleanup handler code: if (... and $dbh-{Active} and !$dbh-{AutoCommit} and eval {$dbh-rollback}) { ... The state of AutoCommit in the connect options seems irrelevant, unless I'm missing something... -John - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On 8/14/07, Perrin Harkins [EMAIL PROTECTED] wrote: On 8/14/07, John Siracusa [EMAIL PROTECTED] wrote: Doesn't Apache::DBI just care that $dbh-{AutoCommit} is false at the time it checks in the cleanup handler? Look further up in the code. It never pushes the handler unless AutoCommit is off. I looked for that and didn't see it, and still don't see it. I'm looking here: if (!$Rollback{$Idx} and Apache-can('push_handlers')) { debug(2, $prefix push PerlCleanupHandler); if (MP2) { my $s = Apache2::ServerUtil-server; $s-push_handlers(PerlCleanupHandler, sub { cleanup($Idx) }); } else { Apache-push_handlers(PerlCleanupHandler, sub { cleanup($Idx) }); } # make sure, that the rollback is called only once for every # request, even if the script calls connect more than once $Rollback{$Idx} = 1; } As far as I can tell, $Rollback{$Idx} is not set elsewhere based on the AutoCommit connect option. This block seems to be entered once for every $dbh. What am I missing? Probably a mistake in my opinion. If others agree, I'll submit a patch to change it. Yeah, I don't think it should care about the connect options, only about the state of the dbh at the time the cleanup is called. -John - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On 8/14/07, John Siracusa [EMAIL PROTECTED] wrote: I looked for that and didn't see it, and still don't see it. Sorry, this was actually changed already. I was looking at an older release. - Perrin - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On Tue, Aug 14, 2007 at 11:57:27AM +0200, Michael Lackhoff wrote: my $db = MyApp::DB-new; $self-my_db($db); # a new db for a new transaction $db-begin_work; # Start transaction ... create two objects and save them ... die Does it roll back?; $db-commit; But when I run this script the two records are saved to the database and not rolled back. Have you also looked at Roes::DB's do_transaction() ? -- Bill Moseley [EMAIL PROTECTED] - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On 14.08.2007 18:54 Perrin Harkins wrote: On 8/14/07, John Siracusa [EMAIL PROTECTED] wrote: The simplest way to do this is to leave everything as per the defaults, and just use Apache::DBI. Note that Apache::DBI also does the automatic rollback for you, but only if you have AutoCommit off when you connect. If you connect with AutoCommit on, you have to handle the rollback yourself. Perhaps I am just doing some stupid mistake but this was exactly where I started off and where it didn't work. Well, I didn't set AutoCommit explicitly, so that might have been on but I use Apache::DBI and at the beginning I had init_db { My::DB-new() } as suggested by John. But then I noticed that there was too much rollback that is: $db-begin_work my $obj = Products-new(db = $db); ... $obj-save; some_helper_that_uses_db(); # rollback here my $obj2 = Customers-new(db = $db); ... $obj2-save; $db-commit; When I ran this code $obj2 was saved but $obj was rolled back. I am sure it was rolled back, because I got a record_id after the save (autoincrement primary key column) but later when I wanted to redirect my app to this record I got an error that there is no record with this id. When I comment out the helper method, both objects are saved and committed. The helper isn't special but as it is it doesn't accept a 'db' argument, so my guess was that when the chain of requests with always the same db was broken, a rollback would occur. This led me to the construct with the db cached in a closure as seen in my first post. - Michael - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On 14.08.2007 20:20 Bill Moseley wrote: On Tue, Aug 14, 2007 at 11:57:27AM +0200, Michael Lackhoff wrote: my $db = MyApp::DB-new; $self-my_db($db); # a new db for a new transaction $db-begin_work; # Start transaction ... create two objects and save them ... die Does it roll back?; $db-commit; But when I run this script the two records are saved to the database and not rolled back. Have you also looked at Roes::DB's do_transaction() ? I know that this is another possible variant but because there is so much code I didn't want to squeeze it into a single call. It would do after some reorganization but I don't think there is much difference between begin_work/commit and do_transaction, at least the docs don't mention it, so I didn't try. - Michael - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On 8/14/07, Perrin Harkins [EMAIL PROTECTED] wrote: On 8/14/07, John Siracusa [EMAIL PROTECTED] wrote: I looked for that and didn't see it, and still don't see it. Sorry, this was actually changed already. I was looking at an older release. Phew. Because I have stuff going to production that depends on my understanding of the current version being correct :) -John - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On 8/14/07, Michael Lackhoff [EMAIL PROTECTED] wrote: $db-begin_work; my $obj = Products-new(db = $db); ... $obj-save; some_helper_that_uses_db(); # rollback here my $obj2 = Customers-new(db = $db); ... $obj2-save; $db-commit; When I ran this code $obj2 was saved but $obj was rolled back. Was there ever a commit after $obj-save but before the rollback in some_helper_that_uses_db()? The helper isn't special but as it is it doesn't accept a 'db' argument, so my guess was that when the chain of requests with always the same db was broken, a rollback would occur. Just to clarify, they're different dbs (i.e., different My::DB objects), but they all share a single DBI $dbh internally, and that's what's important in terms of commit/rollback. -John - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On 14.08.2007 20:34 John Siracusa wrote: On 8/14/07, Michael Lackhoff [EMAIL PROTECTED] wrote: $db-begin_work; my $obj = Products-new(db = $db); ... $obj-save; some_helper_that_uses_db(); # rollback here my $obj2 = Customers-new(db = $db); ... $obj2-save; $db-commit; When I ran this code $obj2 was saved but $obj was rolled back. Was there ever a commit after $obj-save but before the rollback in some_helper_that_uses_db()? No, only the one commit at the very end. I found it very disturbing to see a rollback when everything seemed to be ok. Just to clarify, they're different dbs (i.e., different My::DB objects), but they all share a single DBI $dbh internally, and that's what's important in terms of commit/rollback. It should be but my code behaved as if they wouldn't share the same $dbh, because I use Apache::DBI (should lead to a shared $dbh) but it made a difference if I used the default init_db or the one with the cached db. With the cached db/dbh $obj wasn't rolled back. - Michael - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Still fighting with transactions
On 8/14/07, Michael Lackhoff [EMAIL PROTECTED] wrote: On 14.08.2007 20:34 John Siracusa wrote: On 8/14/07, Michael Lackhoff [EMAIL PROTECTED] wrote: $db-begin_work; my $obj = Products-new(db = $db); ... $obj-save; some_helper_that_uses_db(); # rollback here my $obj2 = Customers-new(db = $db); ... $obj2-save; $db-commit; When I ran this code $obj2 was saved but $obj was rolled back. Was there ever a commit after $obj-save but before the rollback in some_helper_that_uses_db()? No, only the one commit at the very end. Assuming your have AutoCommit turned off, that's why $obj was not saved. The rollback rolled back everything done since the last begin_work(). -John - This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now http://get.splunk.com/ ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object