MySQL 5.6 – Locking and Transactions in Stored Procedure

lockingMySQLmysql-5.6stored-procedurestransaction

Multiple upstream Kapacitor servers are sending notifications to a load-balanced django app. If all the upstream servers are working correctly, the app will always receive duplicates of these notifications (since all the upstream notifiers should send the same notifications; it's just for redundancy). I want to be able to filter out these duplicates. However, since the python app is load-balanced, the only place we can check for these duplicates is in the database.

This means I'm using this stored procedure to control application logic, just inserting the data with the hash into the database and ignoring duplicates is not an option (the application might do something like send someone an SMS message based on the contents of the alert, so we definitely don't want dupes)

To do this, I'm hashing the messages, then invoking a stored procedure in the database that checks if a message received in the last 10 seconds had the same hash. I want to be 99% sure the stored procedure is safe against race conditions.

Here's some SQL code that seems to work:

DROP PROCEDURE IF EXISTS openduty_check_duplicate;
CREATE PROCEDURE openduty_check_duplicate(IN new_hash CHAR(40))
  BEGIN
    DECLARE now TIMESTAMP;
    DECLARE is_duplicate INT;
    SELECT CURRENT_TIMESTAMP INTO now;
    DO GET_LOCK('openduty_check_duplicate', 10);
      START TRANSACTION;
        DELETE FROM openduty_dedup WHERE DATE_SUB(now, INTERVAL 10 SECOND) > triggered;
        SELECT COUNT(*) FROM openduty_dedup WHERE request_hash = new_hash INTO is_duplicate;
        IF is_duplicate = 0 THEN
          INSERT INTO openduty_dedup (request_hash, triggered) VALUES (new_hash, now);
        END IF;
      COMMIT;
    DO RELEASE_LOCK('openduty_check_duplicate');
    SELECT is_duplicate;
  END;

Will this work as intended? DO I need to change the transaction isolation level? Is there a better way to do this in MySQL?

Note I'm using MySQL 5.6 in one environment and Amazon RDS in another environment, so it should be as compatible as possible.

Best Answer

Do these with autocommit=1, with PRIMARY KEY(request_hash):

DELETE FROM openduty_dedup
    WHERE triggered < NOW() - INTERVAL 10 SECOND
      AND request_hash = $hash;
INSERT IGNORE INTO openduty_dedup
    (request_hash, triggered)
    VALUES ($new_hash, NOW());

If the INSERT fails, either there is an entry in the last 10 seconds, or someone else snuck in just now. If you need to know about success, check Rows_affected(), which is connection-specific and does not need to be in a transaction.

No need for START TRANSACTION. No need to pre-compute "now". No need for GET_LOCK(). The only flaw is the hard-coded 10 second window.

Related Question