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.