0

I have a table called 'picks' and When a user logs in and starts making picks, it initially inserts 64 rows into the "picks" table for that user (has the userid as a column). The rows are inserted using a loop in php so there are 64 inserts using PDO prepared statements. This only needs to be done once per user. I need these rows to be consecutive for that user. What if a few other users are doing the same thing at the exact same time? Is there a chance that the rows would not be consecutively inserted? Do I need to use table locks? I've never used them before. MySql version: 5.0.92-rs-log MyISAM thank you

EricP
  • 1,459
  • 6
  • 33
  • 55
  • I'm curious... Why do the records need to be consecutive? – Stefan H Mar 11 '12 at 02:16
  • For some reason it seems to need them to be consecutive. When I wrote this app I was in a rush and perhaps wrote some bad code. I'll have to rewrite some cade at a later date to find out why. – EricP Mar 11 '12 at 02:30
  • if you post your sql for retrieving those rows, we might be able to help you. – Stefan H Mar 12 '12 at 15:48

2 Answers2

0

I wouldn't use table locks for a problem that doesn't actually require them. No rows should need to be consecutive. Maybe you're ordering them based on an autoincremental id? If that is the case they may not be consecutive but they will be in order. Additionally, if you need a particular order on those rows you can always add another column that would set the real order.

Anyway, try expanding your question to get more suitable help.

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
0

First of all, there should be a better way to solve this instead of relying on the IDs being consecutive for a single user.

There is, indeed, the possibility of having a concurrency problem. In other DBMS, like Oracle, you have sequences instead of auto_increment which are better for this type of thing. You could emulate this having an auxiliary table with only one row. In this case, your ID (or whatever) field would NOT be set to auto_increment, and you would have a table called sequence (for example) where you would save the last ID. Then, before inserting the 64 new records, you'll query that table to get the last ID and then update it.

Something like this (don't pay attention to the PHP functions, it's just to make the code understandable):

// Get the last ID
$last_id = query('SELECT last_id FROM sequence LIMIT 1');

// Store the last ID in your code and then update the new value
$new_id = $last_id + 64;
query('UPDATE sequence SET last_id = :new_id', array('new_id' => $new_id));

// Make sure your DB class is set on auto_commit or commit the transaction here

// Insert your new records
for ($i = ++$last_id; $i < $new_id; $i++) {
    query('INSERT INTO picks (id, user_id) VALUES (:id, :user_id)', array('id' => $i, 'user_id' => $user_id));
}
El Barto
  • 919
  • 1
  • 5
  • 18