Prevent another user to insert data at the same time

Question

I have a php script which enables user to write something and after clicking some button the written data will be inserted to the mysql table data along with the current timestamp. Simple enough. Now since I want to pull the data from the table based on the highest timestamp, I expect my query to pull only a single row of data that will be the last row. Keeping this in mind I cannot allow 2 or more users to insert the data at the same time which will generate exactly the same timestamp which in return will go against my purpose while querying the data. Though I know that the odds of generating exactly the same timestamp is very low, but I want to be on the safe side. Here's what I have tried out

$sql_lock_table = $db->prepare("LOCK TABLES comments WRITE");
$sql_lock_table->$db->execute();

$time = time();
$data = "some data";

$sql_insert = $db->prepare("INSERT INTO data VALUES(null, :time, :thedata)");

$sql_insert->execute(array(
    "time" => $time,
    "thedata" => $data
    ));

$sql_unlock_table = $db->prepare("UNLOCK TABLES");
$sql_unlock_table->$db->execute();

I have got the lock and unlock thing from another stackoverflow question but I have not understood clearly why locking the table before inserting, since it wont allow anybody to insert data. And so I figured out that I was right. No insertion is happening. What should I do to achieve what I wanted to do?

score 0 · Answer 1 · answered Dec 15 '14 at 12:10

0

As per the properties of MySQL, It will not allow two data to be inserted at the same time. So you don't need to take any precautions against it.

check the below link

http://dev.mysql.com/doc/refman/5.6/en/internal-locking.html

answered Dec 15 '14 at 12:10

Arun

3,640
7
44
87

I tested this out with another person. And apparently clicking the button exactly , and exactly means exactly at the same time inserts data. That was when I was creating a shopping cart which wont allow the product quantity to be less than 0. So, when we were clicking the Pay Now button at the same time, thee query should then check whether the value of the product is greater than 0 or not. If greater than 0 then the payment button wont show any error and update the quantity--. But on clicking at the same time we did not face any error and the updated table had the quantity "-1" – nick Dec 15 '14 at 12:15

score 0 · Answer 2 · edited May 23 '17 at 10:33

0

can you specify the table engine?

if your table is InnoDB, then 2 insert statements can occur at the same time wheres MyISAM engine, will LOCK table for insert operation, preventing other inserts to occur.

if InnoDB is not required for you, if you will change it to MyISAM, mysql engine will take care that ONLY one insert can occur, and other will block until the current one will finish

checkout: What's the difference between MyISAM and InnoDB

hope that would help a bit...

edited May 23 '17 at 10:33

Community

1
1

answered Dec 15 '14 at 12:39

YyYo

651
5
13

My table is InnoDB not MyISAM.. Can I achieve this in InnoDB? Isn't not the InnoDB for transaction purpose? – nick Dec 15 '14 at 14:06
if InnoDB is not required for any purpose for you, if you will change it to MyISAM, mysql engine will take care that ONLY one insert can occur, and other will block until the current one will finish – YyYo Dec 15 '14 at 16:01

Prevent another user to insert data at the same time

2 Answers2