Re: [BULK] - Re: [Catalyst] flash with DBIC session storage
On 7/27/07, Jonathan T. Rockway [EMAIL PROTECTED] wrote: On Fri, Jul 27, 2007 at 11:57:01AM -0700, Mesdaq, Ali wrote: Are you sure that InnoDB would solve this issue? Even if just a row was locked and you have 2 inserts at the exact same time how would that resolve the issue? One transaction would succeed and the other would fail. If you want different behavior, you'll have to change the isolation level (or actualy, in this case, rethink your app). In custom database code this is true, but usually session APIs handle this kind of thing for you. They typically provide exclusive locking while a session is being used, through SELECT...FOR UPDATE or similar. Even a session module that explicitly doesn't provide exclusive locking should be able to avoid doing a duplicate insert. - Perrin ___ List: Catalyst@lists.rawmode.org Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.rawmode.org/ Dev site: http://dev.catalyst.perl.org/
RE: [BULK] - Re: [Catalyst] flash with DBIC session storage
This has been posted to the DBIC list for discussion last week. Jonathan just to clarify I was not saying create() calls should not throw exceptions I was saying find_or_create() should handle Duplicate Key insert errors better since it supposedly should return an resultset to you if it exists. But there is some discussion on the list about the issue by people a lot smarter than me and I am sure they will have a good solution or explanation for the problem. Thanks, -- Ali Mesdaq Security Researcher II Websense Security Labs http://www.WebsenseSecurityLabs.com -- -Original Message- From: Jonathan T. Rockway [mailto:[EMAIL PROTECTED] Sent: Friday, July 27, 2007 8:57 PM To: The elegant MVC web framework Subject: [BULK] - Re: [Catalyst] flash with DBIC session storage On Fri, Jul 27, 2007 at 06:11:03PM -0700, Mesdaq, Ali wrote: I think in the case of the person who initially emailed the group the problem is poor load testing. But it does bring up the point of better handling of exception statements by DBIx. DBIx:: is a generic namespace for DBI extensions. DBIx::Class is abbreviated as DBIC. (This comes up a lot, I should add it to a FAQ or something.) The DBIx::Class::ResultSet::find_or_create(): call should handle this exception better because it can happen in tons of different places and its not always an application or benchmarking issue. What do you think it should do? create failed. Only your app knows what to do in that situation. Maybe you are suggesting that errors be hidden so your app silently loses data? I am not in favor of that. In high load situations you WILL see this happen. I have a feeling that your app is broken then. I often do dumbass load testing on my apps and don't have any problems with transactions failing unchecked. I really think you should read about transactional isolation and how deadlocks/conflicts are handled by your RDBMS. The Berkeley DB has really good documentation on both of these things, describing both theory and how to avoid common problems with the library itself. SQLite's docs are also good. To illustrate here is an example: Your DB has a first_name table to store user first names You get a flood of new people registering and you normalize their first names to get id's 2 Users have the first name of Tom which does not exist yet You call DBIx::Class::ResultSet::find_or_create(): Both calls detect the name does not exist Both calls insert with one getting this error Now there is no real way to handle this in your app unless you do a lot of concurrency checking or queuing of inserts. Rollback and try again. Your DBMS' docs will tell you about the best strategy for dealing with this case. I'm sure there's a good-enough generic solution... if someone knows it, I would like to hear. Anyway, you should probably continue this thread on the DBIC mailing list instead. DBIC has *nothing* to do with Catalyst. Regards, Jonathan Rockway ___ List: Catalyst@lists.rawmode.org Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.rawmode.org/ Dev site: http://dev.catalyst.perl.org/ ___ List: Catalyst@lists.rawmode.org Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.rawmode.org/ Dev site: http://dev.catalyst.perl.org/ ___ List: Catalyst@lists.rawmode.org Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.rawmode.org/ Dev site: http://dev.catalyst.perl.org/
Re: [BULK] - Re: [Catalyst] flash with DBIC session storage
On Sat, Jul 28, 2007 at 09:15:36AM +0200, Tobias Kremer wrote: I think you're right: The error is due to my poor-man's approach of load-testing. BTW, how are you invoking ab? I think each request should get its own cookie, which means the same id shouldn't be being inserted more than once. Finally, you might want to try siege instead of ab. Regards, Jonathan Rockway ___ List: Catalyst@lists.rawmode.org Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.rawmode.org/ Dev site: http://dev.catalyst.perl.org/
Re: [BULK] - Re: [Catalyst] flash with DBIC session storage
Am 28.07.2007 um 10:12 schrieb Jonathan T. Rockway: On Sat, Jul 28, 2007 at 09:15:36AM +0200, Tobias Kremer wrote: I think you're right: The error is due to my poor-man's approach of load-testing. BTW, how are you invoking ab? I think each request should get its own cookie, which means the same id shouldn't be being inserted more than once. Yep, that was exactly what was causing trouble. I provided a cookie to give ab access to member-only content. --Tobias ___ List: Catalyst@lists.rawmode.org Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.rawmode.org/ Dev site: http://dev.catalyst.perl.org/
RE: [BULK] - Re: [Catalyst] flash with DBIC session storage
Are you sure that InnoDB would solve this issue? Even if just a row was locked and you have 2 inserts at the exact same time how would that resolve the issue? I could see InnoDB solving the issue if updates were being made to the row but not a insert on a new record. Actually if anything it makes more sense to me that a table lock would help prevent issues on new inserts. But I am very interested in hearing your explanation as I have seen this a lot and we use both innodb and MyISAM tables where each makes sense to use. Thanks, -- Ali Mesdaq Security Researcher II Websense Security Labs http://www.WebsenseSecurityLabs.com -- -Original Message- From: J. Shirley [mailto:[EMAIL PROTECTED] Sent: Friday, July 27, 2007 11:38 AM To: The elegant MVC web framework Subject: [BULK] - Re: [Catalyst] flash with DBIC session storage On 7/27/07, Tobias Kremer [EMAIL PROTECTED] wrote: Am 27.07.2007 um 20:14 schrieb J. Shirley: On 7/27/07, Tobias Kremer [EMAIL PROTECTED] wrote: While hammering my site with ab (Apache bench) I'm getting loads of the the following error message: Couldn't render template undef error - DBIx::Class::ResultSet::find_or_create(): DBI Exception: DBD::mysql::st execute failed: Duplicate entry 'flash:4f1bddce6c7828c27b2e47265f614109d4c21f19' for key 1 [for Statement INSERT INTO sessions (id) VALUES (?) with ParamValues: 0='flash:4f1bddce6c7828c27b2e47265f614109d4c21f19'] at /usr/local/lib/perl5/site_perl/5.8.8/Catalyst/Plugin/ Session/Store/DBIC/Delegate.pm line 52 What's your backend RDBMS? Not using MyISAM or something similarly silly, right? Indeed, I'm using MySQL + MyISAM but I've never come across this problem before. Is this some sort of locking issue and I've no choice but switch to InnoDB? --Tobias MyISAM cannot do row level locking, so the only alternative is table locking on writes. So, what happens is you'll have two concurrent write requests with the same session key and they'll clobber each other. The session table really really really should be InnoDB. MyISAM is very poorly suited for such activities. As a general rule of thumb, I tend to always say that unless you know why you should be using MyISAM, you should use InnoDB. Good luck with it though :) -Jay -- J. Shirley :: [EMAIL PROTECTED] :: Killing two stones with one bird... http://www.toeat.com ___ List: Catalyst@lists.rawmode.org Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.rawmode.org/ Dev site: http://dev.catalyst.perl.org/ ___ List: Catalyst@lists.rawmode.org Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.rawmode.org/ Dev site: http://dev.catalyst.perl.org/
RE: [BULK] - Re: [Catalyst] flash with DBIC session storage
I believe this may be an issue with DBIx and multiple inserts from different processes. This is something we have seen when we created our own db abstraction layer. When you have a unique key constraint you we saw that it was possible to check the table for that key and if it doesn't exist to insert it and return the id. But if another process was doing the same thing (checking for it and not finding it and inserting) you would get a collision on the constraint. So our solution was to wrap the call so that on insert if we saw that exact error we would reselect from that table with that constraint and return the id. I believe you will have this issue with any DB that enforces that constraint with multiple processes running with medium to high load. This load test probably would be the ideal place to see this error since its probably using same session id's for various requests. Thanks, -- Ali Mesdaq Security Researcher II Websense Security Labs http://www.WebsenseSecurityLabs.com -- -Original Message- From: Tobias Kremer [mailto:[EMAIL PROTECTED] Sent: Friday, July 27, 2007 11:28 AM To: The elegant MVC web framework Subject: [BULK] - Re: [Catalyst] flash with DBIC session storage Am 27.07.2007 um 20:14 schrieb J. Shirley: On 7/27/07, Tobias Kremer [EMAIL PROTECTED] wrote: While hammering my site with ab (Apache bench) I'm getting loads of the the following error message: Couldn't render template undef error - DBIx::Class::ResultSet::find_or_create(): DBI Exception: DBD::mysql::st execute failed: Duplicate entry 'flash:4f1bddce6c7828c27b2e47265f614109d4c21f19' for key 1 [for Statement INSERT INTO sessions (id) VALUES (?) with ParamValues: 0='flash:4f1bddce6c7828c27b2e47265f614109d4c21f19'] at /usr/local/lib/perl5/site_perl/5.8.8/Catalyst/Plugin/ Session/Store/DBIC/Delegate.pm line 52 What's your backend RDBMS? Not using MyISAM or something similarly silly, right? Indeed, I'm using MySQL + MyISAM but I've never come across this problem before. Is this some sort of locking issue and I've no choice but switch to InnoDB? --Tobias ___ List: Catalyst@lists.rawmode.org Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.rawmode.org/ Dev site: http://dev.catalyst.perl.org/ ___ List: Catalyst@lists.rawmode.org Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.rawmode.org/ Dev site: http://dev.catalyst.perl.org/
Re: [BULK] - Re: [Catalyst] flash with DBIC session storage
On Fri, Jul 27, 2007 at 11:57:01AM -0700, Mesdaq, Ali wrote: Are you sure that InnoDB would solve this issue? Even if just a row was locked and you have 2 inserts at the exact same time how would that resolve the issue? One transaction would succeed and the other would fail. If you want different behavior, you'll have to change the isolation level (or actualy, in this case, rethink your app). It's more of an issue of this: User visits page A. User visits page B in another tab. User submits form A. User submits form B. A updates the flash. B updates the flash. Page A2 loads with whatever flash won. Page B2 loads with whatever flash won. The point is that both transaction A and transaction B can't go through. Web apps aren't designed to be hit concurrently by the same user; it just doesn't make sense (or work) when you're keeping state. One state has to win; this is what the DB error is telling you. You could setup locks over [submit, update flash, display flash]: User submits form A. Form A gets the lock. User submits form B. B submission blocks waiting for the lock. A updates the flash. Page A2 loads. A unlocks. B gets the lock. B updates the flash. ... This adds a lot of complexity, though. Is this really a problem that your users experience, or is it an artifact of poor load testing practices? Regards, Jonathan Rockway ___ List: Catalyst@lists.rawmode.org Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.rawmode.org/ Dev site: http://dev.catalyst.perl.org/
RE: [BULK] - Re: [BULK] - Re: [Catalyst] flash with DBIC session storage
I think in the case of the person who initially emailed the group the problem is poor load testing. But it does bring up the point of better handling of exception statements by DBIx. The DBIx::Class::ResultSet::find_or_create(): call should handle this exception better because it can happen in tons of different places and its not always an application or benchmarking issue. In high load situations you WILL see this happen. To illustrate here is an example: Your DB has a first_name table to store user first names You get a flood of new people registering and you normalize their first names to get id's 2 Users have the first name of Tom which does not exist yet You call DBIx::Class::ResultSet::find_or_create(): Both calls detect the name does not exist Both calls insert with one getting this error Now there is no real way to handle this in your app unless you do a lot of concurrency checking or queuing of inserts. Actually I should write a bug for this so the DBIx guys can check it out. Thanks, -- Ali Mesdaq Security Researcher II Websense Security Labs http://www.WebsenseSecurityLabs.com -- -Original Message- From: Jonathan T. Rockway [mailto:[EMAIL PROTECTED] Sent: Friday, July 27, 2007 5:27 PM To: The elegant MVC web framework Subject: [BULK] - Re: [BULK] - Re: [Catalyst] flash with DBIC session storage On Fri, Jul 27, 2007 at 11:57:01AM -0700, Mesdaq, Ali wrote: Are you sure that InnoDB would solve this issue? Even if just a row was locked and you have 2 inserts at the exact same time how would that resolve the issue? One transaction would succeed and the other would fail. If you want different behavior, you'll have to change the isolation level (or actualy, in this case, rethink your app). It's more of an issue of this: User visits page A. User visits page B in another tab. User submits form A. User submits form B. A updates the flash. B updates the flash. Page A2 loads with whatever flash won. Page B2 loads with whatever flash won. The point is that both transaction A and transaction B can't go through. Web apps aren't designed to be hit concurrently by the same user; it just doesn't make sense (or work) when you're keeping state. One state has to win; this is what the DB error is telling you. You could setup locks over [submit, update flash, display flash]: User submits form A. Form A gets the lock. User submits form B. B submission blocks waiting for the lock. A updates the flash. Page A2 loads. A unlocks. B gets the lock. B updates the flash. ... This adds a lot of complexity, though. Is this really a problem that your users experience, or is it an artifact of poor load testing practices? Regards, Jonathan Rockway ___ List: Catalyst@lists.rawmode.org Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.rawmode.org/ Dev site: http://dev.catalyst.perl.org/ ___ List: Catalyst@lists.rawmode.org Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.rawmode.org/ Dev site: http://dev.catalyst.perl.org/
