[Dcphp-dev] MySQL transaction question

[Hans Lellelid]

There's no need to lock the entire table; a transaction should work just
fine -- and is much less likely to have terrible side-effects.  Perhaps
MySQL has vastly improved their locking system, but I just remember
horror situations with web apps where there would be an error while a
table was locked and the application would start spewing errors for all
the other users in the system.  We'd have to go in and manually remove
stale locks.  Table locks are horrible, medieval technology!

Hans

alex wrote:
> Hello - I think a manual lock on the table would ensure that other
> clients can not alter the table during the query; one can use the
> postfix LOCK IN SHARE MODE.
> 
> 
> Alex.
> On 7/10/07, *SyAD at aol.com <mailto:SyAD at aol.com>* <SyAD at aol.com
> <mailto:SyAD at aol.com>> wrote:
> 
>     Not sure if I'm doing this right, never posted to this list before.
>      
>     Maybe last_insert_id() would be useful without resorting to
>     transactions; here's doc from MySQL 4.1 ref:
>     || 
>     |LAST_INSERT_ID()|, |LAST_INSERT_ID(/|expr|/)|
> 
>     Returns the /first/ automatically generated value that was set for
>     an |AUTO_INCREMENT| column by the /most recent/ |INSERT| or |UPDATE|
>     statement to affect such a column.
> 
>     mysql> *|SELECT LAST_INSERT_ID();|*
>             -> 195
> 
>     The ID that was generated is maintained in the server on a
>     /per-connection basis/. This means that the value returned by the
>     function to a given client is the first |AUTO_INCREMENT| value
>     generated for most recent statement affecting an |AUTO_INCREMENT|
>     column /by that client/. This value cannot be affected by other
>     clients, even if they generate |AUTO_INCREMENT| values of their own.
>     This behavior ensures that each client can retrieve its own ID
>     without concern for the activity of other clients, and without the
>     need for locks or transactions.
> 
>     Steve
>      
>     In a message dated 7/9/2007 2:18:06 PM Eastern Daylight Time,
>     steve at oeic.net <mailto:steve at oeic.net> writes:
> 
>         Hi All,
>         I have a database scenario that I haven't encountered before,
>         and I was hoping to get some feedback. Here's the business logic:
> 
>         I'm building a system for randomizing patients in a clinical
>         trial. A table of possible randomization codes is populated
>         before the trial begins, with the following pertinent fields:
>         randomizationID - auto-incrementing primary Key
>         randomizationCode - the code to be assigned to a patient
>         patientID - a foreign key of the patient assigned the code
> 
>         Hence, before the trial starts, randomizationID and
>         randomizationCode will have data, but patientID will be NULL.
>         Once a patient enters the study, he will be randomized and
>         assigned a randomizationCode from the table by selecting the
>         first randomizationCode that hasn't already been assigned to
>         another patient, then updating the table with the patientID:
> 
>         UPDATE Randomization
>         SET patientID = 4
>         WHERE randomizationID =
>          (SELECT MIN(randomizationID) as ID
>         FROM Randomization`
>         WHERE patientID is null
>         ORDER by randomizationID)
> 
>         Unfortunately, the mysql docs tell me that it is not currently
>         possible to modify a table and select from the same table in a
>         subquery. My worry is that by splitting the above into two
>         queries, I'll run into issues with concurrent clients attempting
>         randomization at the same time, getting the same
>         randomizationID, and one or more randomizations getting
>         overwritten. Would putting the two queries into a transaction
>         prevent the selected row from being read by other clients?
> 
>         I would test this myself, but I'm not exactly sure how to
>         simulate concurrent users programmatically ( with jmeter?). I'm
>         using Mysql 4.1.2.1 <http://4.1.2.1/> and Zend Framework 1.0.0
> 
>         Thanks!
> 
>         -- 
>         Steve Goodman
>         -------------------------------------
>         phone: 443.226.9447
>         fax:   410.662.9447
>         mail:  mailto:steve at oeic.net
>         -------------------------------------
>         The Ohio East India Company
>         http://www.oeic.net/
> 
>         _______________________________________________
>         Dcphp-dev mailing list
>         Dcphp-dev at calypso.tux.org <mailto:Dcphp-dev at calypso.tux.org>
>         http://calypso.tux.org/cgi-bin/mailman/listinfo/dcphp-dev
>         <http://calypso.tux.org/cgi-bin/mailman/listinfo/dcphp-dev>
> 
> 
> 
> 
>     ------------------------------------------------------------------------
>     See what's free at AOL.com
>     <http://www.aol.com?ncid=AOLAOF00020000000503>.
> 
>     _______________________________________________
>     Dcphp-dev mailing list
>     Dcphp-dev at calypso.tux.org <mailto:Dcphp-dev at calypso.tux.org>
>     http://calypso.tux.org/cgi-bin/mailman/listinfo/dcphp-dev
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> Dcphp-dev mailing list
> Dcphp-dev at calypso.tux.org
> http://calypso.tux.org/cgi-bin/mailman/listinfo/dcphp-dev