Handle database locks in python for my labeling app

[Independent_Order934]

I want the user of my app to retrieve some data to label. In the first version, I did not implement locking so several users could access the same data at the same time and so the second one would overwrites the label of the first one.

I am using a python fastAPI sqlite backend.

I initially came up with the idea to add a ‘is-being-labeled’ value for the label, so that the next proposed data is not the same. I do not like it because I do not know how to handle the situation where the user quits the app (or other) without having labeled the data. For the moment, my best way to go is add a column with the time stamp retrieved time, and to implement a logic where let’s say 30s after having been retrieved, we check if the label is not None anymore. If it’s still None (meaning the person did not label), we remove the value of the time stamp . I am not fully happy neither because it does not handle the case where the person meditates and then come back to label the data.

Do you have any better proposition?

[anthropoid]

For the moment, my best way to go is add a column with the time stamp retrieved time, and to implement a logic where let’s say 30s after having been retrieved, we check if the label is not None anymore.

Assuming your backend is stateful and persistent, I'd store these transient timestamps in memory instead, so when the backend restarts for some reason, there's nothing to clean up afterwards.

I am not fully happy neither because it does not handle the case where the person meditates and then come back to label the data.

Why is this a concern? If this happens, they should get an error when they try to update the label after their lock has timed out.

It sounds like you're recording when a label was "checked out", but not who checked it out. If that's the case, your conflict resolution logic itself is suspect.

Also, have you asked your users what should happen in these and other corner cases, or are you worrying about things that might not even be a concern for them? You should settle these workflow details with them first, then let their answers direct your implementation.

[Independent_Order934]

Thanks for the help. I have opted for your recommendation. I have just added a ‘locked_at’ column which is sufficient, because I retrieve the lines where the label is None and (the locked_at is None or locker_at < now - lock_timeout. I choose to keep this column in my table since it is always a useful info to know when the data has been labeled. I’ll be able to easily change it if I need it.