Mysql – Lock thesql table to prevent select and insert at the same time

innodbinsertMySQLselect

I know this question has been asked earlier and I have also read the documentation. But I guess I'm stupid not understand.

I have a code that inserts a row and the row should be unique in several field values. But to make this new data really live delivered to the visitors it updates (if new data, inserting it) via ajax and every 5th second. This means it sometimes insert two or more rows at the same time.

I have a SELECT query before the INSERT to check if the row (a row with the exactly same data) already exist. And I also use BEGIN and COMMIT.
Yet duplicate rows appearing.

I'm using innoDB.

I have googled and found LOCK functions but not figured out how to use it. And does it even work for SELECT? The best would be to LOCK the SELECT query checking if the row already exist.

Best Answer

With LOCK you probably mean locking entire table, that should not be needed in InnoDB. Check SELECT .. FOR UPDATE.

But duplicate values should be forbidden with the use of UNIQUE indexes, that way the worst thing you get is an "duplicate key" error and not a duplicated row.

If you have proper indexes in place, you may even use INSERT .. ON DUPLICATE KEY UPDATE.

Related Solutions

Mysql – find and insert row to another table using thesql trigger

This should do the trick for you:

DELIMITER $$
DROP TRIGGER IF EXISTS `employee_INSERT` $$
CREATE TRIGGER `employee_INSERT` 
AFTER INSERT ON `employee`
FOR EACH ROW
BEGIN
    INSERT INTO employee_tools (Id, Tool)
    SELECT new.Id, tools.Tool_Name 
            FROM tools
            WHERE tools.Division = new.Division;
END $$
DELIMITER ;

Sql-server – Combine – Select Into, with Insert Into Select

When the automation fires off the second time, I get an error because the table already exists and, consequently, the second round of data isn't inserted.

I would suggest you to put a condition to check the existence of the table

If exists (select 1 from sys.tables where name = '' and type = 'U')
begin -- table exists, so insert values in it
insert into table_name values ()
end
else
begin -- table does not exist, so create it
create table table_name (column datatype)
insert into table_name values ()
end

You can even use below sql to create new blank table as the where 1=0 will always be false.

Note that it wont create any indexes from old_table into new_table.

SELECT *
INTO new_table
FROM old_table where 1=0

To capture errors, you can use TRY/CATCH

Best Answer

Related Solutions

Mysql – find and insert row to another table using thesql trigger

Sql-server – Combine – Select Into, with Insert Into Select

Related Question