Is it okay to lock a row for a long period? (days or weeks)-CodePudding

For example, we have the following table:

CREATE TABLE tasks (
  id UUID,
  name VARCHAR
)

Now we want to assign these tasks to a worker, but the worker will need up to 1 month to complete the task.

Is it okay if we get an exclusive lock on the task from the worker and release it on worker failure/completion ?

Creating a separate table tracking workers and assigning workerId to tasks then health checking the workerId with taskId would be another solution.

If there are any tips regarding this, I would love to hear about them, Thanks in advance.

CodePudding user response：

If you keep a database lock for such a long time, you have to keep a transaction open for a long time. That is going to hurt your database, because autovacuum won't be able to clean up, and concurrent sessions may get stuck behind the lock.

I recommend that you add another column to the table that indicates if the row is "locked" or not.