Multiple upstream 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 (unless we did distributed locking, etc, which sounds like more of a pain).
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 know about hash collisions; it's a risk). I want to be 99% sure the store procedure is safe against race conditions.
Here's the SQL code that seems to work:
DROP TABLE IF EXISTS openduty_dedup; CREATE TABLE openduty_dedup ( request_hash CHAR(40) NOT NULL UNIQUE PRIMARY KEY, triggered TIMESTAMP NOT NULL); -- main procedure for checking if a notification is a duplicate -- given the SHA-1 hash of a notification, it will return 1 if the -- same notification was received in the last 10 seconds (and was not -- treated as a duplicate then), otherwise it will return 0 DROP PROCEDURE IF EXISTS openduty_check_duplicate; CREATE PROCEDURE openduty_check_duplicate(IN new_hash CHAR(40)) BEGIN DECLARE last_trigger TIMESTAMP; DECLARE now TIMESTAMP; DECLARE is_duplicate INT; SELECT CURRENT_TIMESTAMP INTO now; DO GET_LOCK('openduty_check_duplicate', 10); SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION; SELECT triggered FROM openduty_dedup WHERE request_hash = new_hash INTO last_trigger; IF last_trigger IS NULL THEN INSERT INTO openduty_dedup (request_hash, triggered) VALUES (new_hash, now); SELECT 0 INTO is_duplicate; ELSEIF DATE_SUB(now, INTERVAL 10 SECOND) > last_trigger THEN UPDATE openduty_dedup SET triggered = now WHERE request_hash = new_hash; SELECT 0 INTO is_duplicate; ELSE SELECT 1 INTO is_duplicate; END IF; DELETE FROM openduty_dedup WHERE DATE_SUB(now, INTERVAL 1 MINUTE) > triggered; COMMIT; DO RELEASE_LOCK('openduty_check_duplicate'); SELECT is_duplicate; END;
I'm much more worried about correctness than performance since notifications will only come on the order of every few seconds. In particular, I'm worried that the commit won't happen until after the lock is released.
Will this work as intended?