0

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.

3 Answers 3

2

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.

2
  • Yes I want to lock the entire table. And yes I have read that it shouldn't be needed in innoDB then using transations. But somehow it doesn't work. Maybe this locking doesn't affect reading (SELECT)? The UNIQUE index should and will be used from now on. But, unfortunately, there are several tables affected in this process, stats being updated with +1 and so on. So the best would be to make this SELECT with it's if-statement checking for the whole process to begin to be locked. Sep 25, 2015 at 20:27
  • You need to issue a WRITE lock on the table(s) to forbid reading from other threads. Using FOR UPDATE "blocks" only other selects which are FOR UPDATE too (and inserts/updates/deletes). It does not block "normal" read-only selects in the default isolation. It has to be INSIDE a started transaction (otherwise the lock is released in the moment the select finishes) and it does lock only some rows, not entire table (but that is enough to assure consistent data).
    – jkavalik
    Sep 26, 2015 at 5:28
1

You could try this in your session

SET autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION
SELECT ...
INSERT ...
COMMIT;

SERIALIZABLE causes SELECTs to do the locking for you

Here is what the MySQL Documentation says on SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommitis disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

If you close your session, the next session you open will have autocommit back at 1. That means you will have to disable autocommit and set SERIALIZABLE each time.

Perhaps READ COMMITTED would be better

SET autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION
SELECT ...
INSERT ...
COMMIT;

Documentation Says

READ COMMITTED

A somewhat Oracle-like isolation level with respect to consistent (nonlocking) reads: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. See Section 14.2.2.2, “Consistent Nonlocking Reads”.

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE statements, and DELETE statements, InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records.

At any rate, try a different transaction isolation level.

NOTE: Try it on a Staging Server first.

1

If the table is declared with

UNIQUE(x,y,z)

Then you are totally protected and you don't need to do anything. No Locks. You will never get two rows with the same combination if x,y,z.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.