From owner-freebsd-questions@FreeBSD.ORG Wed Mar 26 01:46:20 2008 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id 3D634106566B for ; Wed, 26 Mar 2008 01:46:20 +0000 (UTC) (envelope-from rock_on_the_web@comcen.com.au) Received: from angel.comcen.com.au (angel.comcen.com.au [203.23.236.69]) by mx1.freebsd.org (Postfix) with ESMTP id D8EBB8FC12 for ; Wed, 26 Mar 2008 01:46:19 +0000 (UTC) (envelope-from rock_on_the_web@comcen.com.au) Received: from [192.168.0.199] (202-172-126-254.cpe.qld-1.comcen.com.au [202.172.126.254]) by angel.comcen.com.au (8.13.4/8.12.9) with ESMTP id m2Q1dDTG015424 for ; Wed, 26 Mar 2008 12:39:15 +1100 (EST) From: Da Rock To: freebsd-questions@freebsd.org In-Reply-To: <34394a3a0803231701n3e125b15nfa866a9dfccfb331@mail.gmail.com> References: <1206313415.6973.78.camel@laptop2.herveybayaustralia.com.au> <20080323191727.bd9c5237.wmoran@potentialtech.com> <1206315963.6973.84.camel@laptop2.herveybayaustralia.com.au> <34394a3a0803231701n3e125b15nfa866a9dfccfb331@mail.gmail.com> Content-Type: text/plain Date: Wed, 26 Mar 2008 11:39:07 +1000 Message-Id: <1206495548.6973.161.camel@laptop2.herveybayaustralia.com.au> Mime-Version: 1.0 X-Mailer: Evolution 2.12.3 (2.12.3-3.fc8) Content-Transfer-Encoding: 7bit X-comcen-MailScanner-Information: Please contact the ISP for more information X-comcen-MailScanner: Found to be clean X-comcen-MailScanner-SpamCheck: not spam, SpamAssassin (not cached, score=-16.428, required 4, autolearn=not spam, ALL_TRUSTED -1.80, AWL 0.37, BAYES_00 -15.00) X-comcen-MailScanner-From: rock_on_the_web@comcen.com.au Subject: Re: OT: (Way OT) PHP and MySQL concurrency control using MyISAM tables X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 26 Mar 2008 01:46:20 -0000 On Sun, 2008-03-23 at 17:01 -0700, Patrick C wrote: > MyISAM supports locking (like all engines) but not transactions. Without > transactions, you can do a lock lock a table or tables, and unlock them, > however you cannot roll back statements -- so if a statement down the line > fails for some reason there is no way to rollback and undo past statements > (automagically at least) > > The simple solution is to use InnoDB, which supports Good Things you want - > it's more scalable across multiple threads, row-level locking, transactions, > foreign keys, etc. > > The differences are fairly well documented. It sounds like you're using PDO, > please read up on auto-commit mode. Don't reinvent the wheel, especially > when the wheel is already built better than you could hack out a replacement > for it :) > > -Patrick > > On 23/03/2008, Da Rock wrote: > > > > > > On Sun, 2008-03-23 at 19:17 -0400, Bill Moran wrote: > > > Da Rock wrote: > > > > > > > > I know this is not quite the list for these things, but I tried the > > PHP > > > > list and got no reply whatsoever. In fact, I don't think anyone's home > > > > cause the entire list is silent... > > > > > > > > I'm trying to setup a system using web apps in PHP using MySQL as the > > > > backend database, only this time I need transaction services. > > According > > > > to the PHP manual if a transaction is served for MySQL it can come > > back > > > > as committed even though it may not. So what I'm trying to accomplish > > is > > > > develop some row level locking with the PHP script. > > > > > > > > I enquired about setting up a servlet (for want of a better term) with > > > > PHP, something that will serve the requests of the rest of the app. To > > > > be honest though, I'm not entirely sure how to approach this. > > > > > > Wow. That's one crazy attempt at a workaround. > > > > > > The correct solution is to use the correct tool for the job. Either > > > install PostgreSQL and use it instead, or use InnoDB tables. > > > > > > > > > Actually, I think I may have got some facts confused here- I thought > > that MyISAM was not supposed to be transaction supported, but according > > to most stuff I've read it supports table level transaction locking. > > > > And the PHP manual says it will only come back with a false commit IF > > the table DOESN'T support transactions at all. > > > > So what is the truth here? If MyISAM supports transaction table locking > > I may be ok here- and save myself a hell of a lot of trouble to boot. > > > > Thanks guys, again. > > I remember now exactly why I wanted MyISAM- you see the table locking is exactly what I need for the task. I just need to come up with a method to ensure what I send to the server does actually get written- or am I just being paranoid? The task I require needs to offer direct sequential access with no undoing of written data. And given the legality of the task based on these strict requirements, you can understand my paranoia.