2

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

  1. C1 reads the resource and checks the lease time to make sure current time >= leasetime + T

  2. C1 writes timestamp of current time to lease column

End db transaction

  1. C1 starts using the resource

  2. C2 reads the resource and checks the lease time but current time < leasetime + T so resource is busy

  3. C2 returns error

  4. C1 is finished and writes current time - T to lease

  5. 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?

  • Your DB engine probably has something usable out of the box to handle this problem. What RDMS do you use? Anyway timeouts are a very common thing, and unless your server actively monitor the clients I don't see how to avoid them (at a reasonable cost, that is). – user44761 Dec 11 '17 at 12:44
  • The way this question is worded I get the impression that C1 is different from C2 .. Cn. And that you are only concerned with restarting C2..Cn. But what happens if C1 crashes? Also, C1 and C2 are communicating .. they are doing so through the DB. – Peter M Dec 11 '17 at 12:48
  • I'm using Postgres. C1 and C2 are just different instances. I've been looking at different locking mechanisms in Postgres but they don't seem to fit my use case where the work done can take quite some time and involves network requests. – span Dec 11 '17 at 12:55
  • If nothing fits your Use Case it may be that you are trying to fit a square peg into a round hole. On the other hand there may to another way to look at this. Your problem seems similar to control of the token in token passing networks, and the mechanism used to recover the token if it is lost. But that is just a thought. – Peter M Dec 11 '17 at 13:03
  • 1
    Cheers. I'm afraid I can't help you there since I suck at RDBMs stuff. But the general idea is that you model an atomic transaction that has to both compare and swap values or compare and set values. If the comparison fails against the expected value, the value doesn't get set and the whole thing fail as a whole. – user204677 Dec 13 '17 at 8:16
1

C1 doesn't crush case

Start db transaction (make sure to use explicit locking!)

  1. C1 reads the resource and checks that is_busy = false

  2. C1 writes is_busy = true and leased_at with current timestamp

End db transaction

  1. C1 starts using the resource

  2. C2 reads the resource and sees that is_busy is true

  3. C2 returns error

  4. C1 is finished and writes is_busy = false

  5. C2 tries again (step 1-3) and can now lease the resource

C1 crushes case

Start db transaction

  1. C1 reads the resource and checks that is_busy = false

  2. C1 writes is_busy = true and leased_at with current timestamp

End db transaction

  1. C1 crushes

  2. C2 reads the resource and sees that is_busy is true

  3. C2 returns error

  4. Some separate thread/process/service/database itself (smth like pgAgent) monitors if is_busy was locked for too long by checking leased_at value. The query could be like update table set is_busy = false where is_busy = true and leased_at < now() - interval '15 minutes'

  5. C2 tries again (step 1-3) and in 15 minutes can lease the resource

|improve this answer|||||
  • On a hunch I just check and you can schedule jobs in MSSQL, MySQL and Postgres on a timed basis from within the DB (mechanism will vary with DB product). So it is likely the OP will be able to do this. – Peter M Dec 11 '17 at 12:56
  • Thanks. I don't see the need for the is_busy column, just having the timestamp should be enough should it not? But using the database time should be fine then? – span Dec 11 '17 at 20:34
  • @span see my edit. – Zapadlo Dec 12 '17 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.