[UPHPU] Using a Database for Object Storage (Sad Times)

[Alvaro Carrasco]

Hi Kirk,

Kirk Ouimet wrote:
> Hi List,
>
>  
>
> I am currently storing an object in a MySQL database. The object is
> continually being updated by many (possibly simultaneous) requests. Workflow
> looks something like this:
>
>  
>
> 1.       Data comes in from one of many sources
>
> 2.       PHP reads the object from the database
>
> 3.       PHP adds data to the object
>
> 4.       PHP writes the object back to the database
>
>  
>
> I think I'm running into a concurrency issue where I have record of new data
> being received, but the database object does not reflect the new data. I
> feel like I need to make the object a mutex but I have no idea how to go
> about doing that with PHP. Can anyone provide alternative designs that will
> be a little more robust and handle concurrency?
>
>   

Try wrapping the select and the update in one transaction. And doing a 
SELECT ... FOR UPDATE:

$pdo->beginTransaction();
$stmt = $pdo->query("SELECT obj FROM table_with_object WHERE object_id = 
1 FOR UPDATE");
// ...
// get the object out of the statement and have php add the data to it
// ...
$pdo->exec("UPDATE table_with_object SET object = '$obj' WHERE object_id 
= 1");
$pdo->commit();

This is just a guess, i haven't tested it. Also make sure you use 
prepared statements to prevent SQL injection, I didn't here for the sake 
of brevity.

Alvaro