MySql Transactions

Question

So I have two methods. Method one adds a row to my database. Method two selects and updates the latest 10 rows of the table.

If method one adds a new row after method two selects the data but before it updates it will method two update the wrong 10 rows? Will transactions help here or do i need to lock tables?

function one(){
    insert row
}

function two(){
    select latest 10 rows
    update latest 10 rows
}

// EDIT /////////////////////////////////////////////////////////////////////////////

I was reluctant to throw in my actual methods (they are codeIgniter methods) but here they are

function one(){
    insert row
}

function two(){
    $this->db->where('status', '1');
    $this->db->limit(10);
    $query = $this->db->get('rrsf_users');

    $this->db->where('status', '1');
    $this->db->limit(10);
    $this->db->update('rrsf_users', array('status' => '2'));    
}

So it looks like two separate queries hence the "current last 10 rows" problem. Not sure the best fix for this. Lock tables or cycle though each index selected from the query and add this to the where query?

Answer 1

you won't have a problem.

when you select 10 rows, you know the rows you need to update. what's the purpose of updating latest 10 rows, when you can update those rows which select returned? or if you need to update 10 latest rows, what's the purpose of the select?

Answer 2

It is dependend on how you select those 10 rows. If you have a unique index on them and you know which 10 rows to update (and what you update is exactly those 10 rows previously selected) then you do not have a problem. Of course if you write the update statement in a way that you update also the just inserted row you have that problem. Locking as a matter of fact does not help you much here because you only can lock what is already there.

In short: If you know what you update you have no problem. If you update the "current last 10 rows" you have a problem.

Answer 3

You could use LOCK for this.

SELECT * FROM bla ORDER BY foo DESC LIMIT 10 FOR UPDATE

will select last entries and lock them until you have updated them.

issuing UPDATE bla [...] will remove the LOCK

Answer 4

The question is confusing. If you're selecting the latest 10 rows from a table, then you're inserting another row into that table, what is returned from your SELECT doesn't change.

Do you want to update the 10 selected prior to the insert? If so, update the rows that came back that are cached, and keep their ID's to go back and UPDATE them. If you want to update the last 10 rows in general, then:

1. Why are you doing the SELECT? Insert the row first and then called two().

2. It should be fine the way it is now.

Answer 5

Ok, this is what I ended up with. Could'nt work out the lock tables this so went with updating the index.

$this->db->where('status', '1');
$query = $this->db->get('rrsf_users', 10);
$result = $query->result_array();
foreach ($result as $value) {
    $this->db->or_where('id_user', $value['id_user']); 
}           
$this->db->update('rrsf_users', array('status' => '2')); 
return $query->result_array();