Mysql – Do I need a locking read

innodblockingMySQLtransaction

I am inserting rows in a table, based on a value and on the existence of a row, which I get from SELECT statements. This function can be run concurrently, so I need to do some extra obviously. The basic steps are (pseudocode)

function doStuff(int someID){
    found = `SELECT someIDFROM targettable` 
    if(found){
        /*we have done this allready, so skip.*/
    }else{
       value =' SELECT value FROM statetable`
       //insert value;
        `INSERT INTO targettable
        VALUES (someID,value)'
    }
 }

The problem is ofcourse that if process1 reads uniqueID, figures it should insert the value, and process2 does the same before the write is finished, I get 2 lines in my targettable. This is possible (someID is not unique for that table), but in this case unwanted because it was done on wrong assumptions because of the concurrency.

Now my assumption/feeling is that a transaction alone is not enough for this. The second process will have consistent reads I think, but that does not help prevent the second INSERT, does it? so this will not do (I think, please advice me on this. This could be where I'm going wrong). (I'm quite sure at the moment this is not enough to ensure correct behaviour, because of testing)

function doStuff(int someID){
    'START TRANSACTION HERE'
    found = `SELECT someIDFROM targettable where someid = someID ` 
    if(found){
        /*we have done this allready, so skip.*/
    }else{
       value =' SELECT value FROM statetable`
       //insert value;
        `INSERT INTO targettable
        VALUES (someID,value)'
    }
    COMMIT TRANSACTION
}

From what I read about InnoDB locking reads it could be that I should use those, but I'm not sure as I have never worked with them. It could also be the transaction is enough, and I'm just begin foolish.

My current idea is to use SELECT someID FROM targettable FOR UPDATE, which I figure will give me a lock. But I'm not sure what lock, since it depends on isolation level. This would look like so:

function doStuff(int someID){
    found = `SELECT someIDFROM targettable where someid = someID FOR UPDATE` 
    if(found){
        /*we have done this allready, so skip.*/
    }else{
       value =' SELECT value FROM statetable`
       //insert value;
        `INSERT INTO targettable
        VALUES (someID,value)'
    }
 }

I'm not sure how to cope with the value I get from another table, but that might be another thing.

Summarizing: Can I get away with just using a transaction, do I need to use a locking-read (if so, how?) or is there another option I missed?

Update: If I do something like

START TRANSACTION;
SELECT * FROM targettable WHERE someid = 3 FOR UPDATE

And query that select in another thread, I get no result (locked?) until this transaction is finished. But if I put an INDEX on that someid, I DO! I'm probably missing something, but I wasn't expecting that.
How can I be sure I don't insert this row twice?

Best Answer

I have a suggestion that could simply your code and your SQL

You are doing round trips at this point by checking each individual someID, coming back to the client, seeing if the check failed. If the check failed, it is OK to perform the INSERT. That's another round trip. You will require some kind of locking for reads.

If you have to INSERT 10000 rows:

In the best case, you run 10000 SELECTs are find that someID exists
In the worst case, you run 10000 SELECTs, each someID does not exist, and do 10000 INSERTs.

Why not just perform all this with a single SQL statement ?

function doStuff(int someID) {
    getvalue = `SELECT value FROM statetable`
    INSERT IGNORE INTO targettable VALUES (someID,getvalue);
}

This works only if someID is unique in targettable

In your code, try submitting a list of someID values, iterate that list, and submit INSERT IGNORE commands for all someID values. That you can do in a START TRANSACTION/COMMIT block.

function doStuff(int someIDList) {
    int idcount = 0;
    getvalue = `SELECT value FROM statetable`
    for someID in someIDList
    do
        if idcount = 0 then
            START TRANSACTION
        endif;
        idcount++;
        INSERT IGNORE INTO targettable VALUES (someID,getvalue);
    end while;
    if idcount > 0 then
        COMMIT
    endif;
}

Related Solutions

Mysql – InnoDB row locking – how to implement

What you want is SELECT ... FOR UPDATE from within the context of a transaction. SELECT FOR UPDATE puts an exclusive lock on the rows selected, just as if you were executing UPDATE. It also implicitly runs in READ COMMITTED isolation level regardless of what the isolation level is explicitly set to. Just be aware that SELECT ... FOR UPDATE is very bad for concurrency and should only be used when absolutely necessary. It also has a tendency to multiply in a codebase as people cut and paste.

Here's an example session from the Sakila database which demonstrates some of the behaviors of FOR UPDATE queries.

First, just so we're crystal clear, set the transaction isolation level to REPEATABLE READ. This is normally unnecessary, as it is the default isolation level for InnoDB:

session1> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
session1> BEGIN;
session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA      | WILLIAMS  |
+------------+-----------+
1 row in set (0.00 sec)

In the other session, update this row. Linda got married and changed her name:

session2> UPDATE customer SET last_name = 'BROWN' WHERE customer_id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Back in session1, because we were in REPEATABLE READ, Linda is still LINDA WILLIAMS:

session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA      | WILLIAMS  |
+------------+-----------+
1 row in set (0.00 sec)

But now, we want exclusive access to this row, so we call FOR UPDATE on the row. Notice that we now get the most recent version of the row back, that was updated in session2 outside of this transaction. That's not REPEATABLE READ, that's READ COMMITTED

session1> SELECT first_name, last_name FROM customer WHERE customer_id = 3 FOR UPDATE;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| LINDA      | BROWN     |
+------------+-----------+
1 row in set (0.00 sec)

Let's test out the lock set in session1. Note that session2 cannot update the row.

session2> UPDATE customer SET last_name = 'SMITH' WHERE customer_id = 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

But we can still select from it

session2> SELECT c.customer_id, c.first_name, c.last_name, a.address_id, a.address FROM customer c JOIN address a USING (address_id) WHERE c.customer_id = 3;
+-------------+------------+-----------+------------+-------------------+
| customer_id | first_name | last_name | address_id | address           |
+-------------+------------+-----------+------------+-------------------+
|           3 | LINDA      | BROWN     |          7 | 692 Joliet Street |
+-------------+------------+-----------+------------+-------------------+
1 row in set (0.00 sec)

And we can still update a child table with a foreign key relationship

session2> UPDATE address SET address = '5 Main Street' WHERE address_id = 7;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

session1> COMMIT;

Another side effect is that you greatly increase your probability of causing a deadlock.

In your specific case, you probably want:

BEGIN;
SELECT id FROM `items` WHERE `status`='pending' LIMIT 1 FOR UPDATE;
-- do some other stuff
UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `id`=<selected id>;
COMMIT;

If the "do some other stuff" piece is unnecessary and you don't actually need to keep information about the row around, then the SELECT FOR UPDATE is unnecessary and wasteful and you can instead just run an update:

UPDATE `items` SET `status`='working', `updated`=NOW() WHERE `status`='pending' LIMIT 1;

Hope this makes some sense.

Mysql – When do I need transactions /locks

In general when running queries (SQL Statements) against a database you want all of the statements to successfully completed and if they do not revert data to what is was before you ran the sql statement. The overall concept you are looking for is ACID

In order to provide this functionality, Databases generally support the concept of a transaction.

When you start a SQL Statement with a Transaction you are telling the database that all statements within the transaction must complete successfully and if they do not the database will rollback(revert) your changes putting the database back to the way it was before you made a change.

Take the following two statements:

Insert INTO UserName VALUES (123,'Test User')
Insert INTO UserCommission VALUES (123,0.5)

If you run the above statements without a transaction and the first statement failed then the second statement will still run thus inserting a row into the UserCommission table without an associated row in the UserName table.

If you then run the following

BEGIN TRANSACTION
Insert INTO UserName VALUES (123,'Test User')
Insert INTO UserCommission VALUES (123,0.5)
IF NO Errors Commit ELSE Rollback

In this scenario if the first statement failed you can check for errors and then rollback the transaction (sql statements) leaving the database as though you had never run the statement so you no longer have a row in the UserCmomission table without a corresponding row in the UserName table.

In general you only need a transaction when you are actually changing the data such as Insert, Delete, Update etc but sometimes as part of procedures you may have select statement to get some data that maybe use in Insert, Delete, Update etc statements.

Locks on the other had deal with how the database actually internally marks that the data is in use so that if other user are querying the same data the database knows that the data is in use.

Depending on how your table is constructed (keys, indexes etc), the size of the table etc and the type of action you are performing ( Select, Insert, Update, Delete) the database will perform many different types of locks such as page, table, row locks etc

Of course all of these can impact the performance of your queries and/or database.

Best Answer

Related Solutions

Mysql – InnoDB row locking – how to implement

Mysql – When do I need transactions /locks

Related Question