Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

concurrence in mysql with phalcon

i'm inserting records in a MySQL database that depend from a previus record, the thing is that when i try to insert records at the same time from different computers it goes crazy

I read about concurrence or transactions but I don't understand how to use it



3.1k
edited May '14

this is an example

I have this record saved on cars: 3, 5, 6,7,8 (which represent the position and the numbers of the cars, example: 3 comes first, 5 second, 6 third, ...)

and I have to bet on wich car is going to win against another, like this

5 over 3 (car number 5 will defeat number 3)

and i have to update the status of the old record to status=0

and save the new record on cars will be: 5,3,6,7,8 (cause of the bet 5 becomes the first place and 3 becomes the second, the other remains in the same position)

but if i try to do it from different computers at the same time, for example

i have this record 3,5,6,7,8

i bet from computer 1: 5 over 3

coputer 2: 6over 3

computer 3: 8 over 3

the new record becomes something like this 6,3,3,7,3 when it sould be (5,3,6,7,8 or 6,5,3,7,8 or 8,5,6,7,3)

depending on which is the first bet accepted by the game

this is a project for the university and its making me crazy



85.4k
Accepted
answer
edited May '14

You can use transactions to save the whole set of records avoiding other processes to take consecutives from the table at the same time as a process that already started.

http://stackoverflow.com/questions/974596/what-is-a-database-transaction

<?php

class RobotsController extends Phalcon\Mvc\Controller
{
    public function saveAction()
    {
        $this->db->begin(); // start a transaction

        $robot = new Robots(); //create a robot

        $robot->name = "WALL·E";
        $robot->created_at = date("Y-m-d");
        if ($robot->save() == false) {
            $this->db->rollback();
            return;
        }

        $robotPart = new RobotParts(); // create robot parts
        $robotPart->robots_id = $robot->id;
        $robotPart->type = "head";
        if ($robotPart->save() == false) {
            $this->db->rollback();
            return;
        }

        $this->db->commit(); // commit transaction to database
    }
}

http://docs.phalconphp.com/en/latest/reference/models.html#manual-transactions