MySQL Multiple processes selecting same data

Question

I have multiple processes running the exact same script, and the problem is that they are selecting the same data. I have a flag "in_use", which is set to 1 after being selected, but the other processes are too fast and selects the same data before the script itself has been able to update all the data to in_use = 1. How can I, perhaps inside of the query, make sure that the same data is never selected? Locking it somehow?

Right now I update in_use for each process, but it's not fast or secure enough.

DELIMITER //

DROP PROCEDURE IF EXISTS `getDomainsForWhois`;

CREATE PROCEDURE `getDomainsForWhois`(
    IN in_tld VARCHAR(10), 
    IN in_max_limit INT
) 

BEGIN 
    SELECT 
        d.domainID, 
        CONCAT(SUBSTRING_INDEX(REPLACE(d.domainName, LOWER(d.tld), ''), '.' , -1), LOWER(d.tld)) as domainName, 
        d.tld 
    FROM 
        Domains d 
    WHERE (
        d.parentDomainID IS NULL 
    AND 
        d.tld = in_tld 
    AND 
        d.dateFetched <= DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL 1 DAY) 
    AND 
        d.in_use = 0 
    ) 
    GROUP BY 
        domainName 
    ORDER BY 
        d.dateFetched ASC 
    LIMIT 
        0,in_max_limit
    FOR
        UPDATE;
END//

This is basically how I run the script. This script runs simaltaneously from multiple processes (cron):

domains=`mysql -u USER -pPASSWORD DBNAME --skip-column-names -e "LOCK TABLE Domains WRITE; CALL getDomainsForWhois('.$1', $2)"`;
while read domainID domainName domainTld
do
        mysql -u USER -pPASSWORD DBNAME -e "UPDATE Domains SET in_use = 1 WHERE domainID = $domainID";
done << EOF
$domains
EOF

mysql -u USER -pPASSWORD DBNAME -e "UNLOCK TABLES;";

Could you just use a select query? `Update tbl set MyLock = UniqueIDForPRocess then Select * from tbl where MyLock = UniqueIDForProcess — Toby Allen, Mar 27 '13 at 14:08

score 0 · Answer 1 · answered Mar 27 '13 at 13:28

0

It would probably be possible to look into just locking the table

http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html

So before the SELECT you do

LOCK TABLE Domains WRITE;

And after you do

UNLOCK TABLES;

answered Mar 27 '13 at 13:28

Simon at The Access Group

6,932
22
45

Will that cause all other processes to fail, or will they get there data as soon as it's unlocked? – user2215771 Mar 27 '13 at 13:33
Any other requests would wait until the lock is released. They then may also lock the table. Realistically though this will have the effect of making the multiple processes pretty much useless unless the processes do a lot of processing with the data they retrieve, as each individual process will be there waiting for lock to be released. The alternative is to do something within the script(s) that call the procedure that chooses precisely what they select instead of relying upon this. – Simon at The Access Group Mar 27 '13 at 13:34
The locking doesn't seem to work when I'm using "SELECT d.* FROM Domains d", only when not giving the table an alias (in this case 'd'). How can I get around that? – user2215771 Mar 27 '13 at 13:46
Do the processes share a single MySQL session? If so that might be the reason. I'd advise taking a look through the document I linked as it covers a lot of information as to how the locks behave. – Simon at The Access Group Mar 27 '13 at 13:52
I'm not sure. I added some code on how I run the queries if it's of any help. – user2215771 Mar 27 '13 at 14:05
Logically I don't think that will work, I assumed you were using it as a stored procedure as per your original post? It may not work the same when you issue the commands in the CLI individually. I kind of meant having the LOCK within the stored procedure. – Simon at The Access Group Mar 27 '13 at 14:20

MySQL Multiple processes selecting same data

1 Answers1