ambitioninc/django-db-mutex

Lock deletion code should use SQL server timestamp

markerrj opened this issue · 2 comments

markerrj commented

https://github.com/ambitioninc/django-db-mutex/blob/develop/db_mutex/db_mutex.py#L83

Here the code deletes locks based on a datetime.now() calculation. I believe this means that if all servers aren't properly NTP-time-synced, the locks could be accidentally deleted sooner than expected on servers with time set in the future.

This code should be doing lock deletion based on the SQL server knowledge of time.

wesleykendall commented

Do you know how to do this through the ORM? Or will this have to be done with raw SQL? I'm assuming that auto_now_add uses the server's time, so that's why this would be an issue when using datetime.utcnow(). Correct? Thanks for the input

markerrj commented

I'm not a Django / SQL expert, but I looked around, and I only could come up with one ANSI SQL portable way of doing this.

CURRENT_TIMESTAMP is part of ANSI SQL 92. ANSI SQL 92 also supports adding or subtracting an INTERVAL to a DATETIME.

https://docs.djangoproject.com/en/1.5/ref/models/querysets/#extra

The extra method would allow specifying a raw SQL where clause. I believe that you would want to be able to convert the django-db-mutex timeout to a SQL 92 INTERVAL format.

Doing this would allow you to construct a WHERE clause to find locks that had a creation timestamp lte CURRENT_TIMESTAMP - INTERVAL.

Again, if my company ends up using this library, hopefully we can contribute some code back. But let me know if you have any thoughts / improvements on the above.