I have a shared resource in a database that is used by clients C1 and C2. Before the resource is read by C1, C1 should mark the resource as leased so that C2 will know it is busy. C1 will do some work and when it is finished it will update the lease to make the resource available to C2. I am using a lease so that if the host system of Cx crashes, another C will be able to use the resource after time T.
The flow is like this:
Start db transaction
C1 reads the resource and checks the lease time to make sure
current time >= leasetime + T
C1 writes timestamp of current time to lease column
End db transaction
C1 starts using the resource
C2 reads the resource and checks the lease time but
current time < leasetime + Tso resource is busy
C2 returns error
C1 is finished and writes
current time - Tto lease
C2 tries again (step 1-3) and can now lease the resource
C1 and C2 cannot communicate.
My naive implementation is to tag the resource with a timestamp from the host system to mark it as lease. When C2 tries to read the resource, it will realise that the timestamp is within the time T and that means the resource is busy. C2 will then return an error to the user and ask them to try again. This solution will not work since it depends on the system time of the host systems for C1 and C2 and that time may differ.
One thought I had was to use the database time to make the calculations of when the resource is leased or free to use. Being dependent on time still seems like a bad solution though.
How can I design this to make sure Cn will not use the resource until C1 is finished?