CODEWITHSUNDEEP
javamysqlspringspring-boot
jason manted
jason manted

Reputation: 1

Best approach to lock editing certain record in DB

I am working on a spring boot project, the task is: I should lock editing capability of product for 15 minutes after creation, so basically if the user create a product, this product will be locked for editing for 15 minutes, after that it can be changed or deleted from the DB. My question is: what is the best approach to achieve that:

1- Should I add a field to the DB table called lastUpdate and then check if the time of 15 minutes exceed.

2- Should I save all the newly created products in array and clear this array every 15 minutes.

or there is any better ways in regard to performance and best practice??

I am using springboot with JPA & mysql.

Thanks.

Upvotes: 0

Views: 361

Answers (4)

Mats Andersson
Mats Andersson

Reputation: 397

You could put your new products in an "incoming_products" table and put a timestamp column in that table that you set to date_add(now(), INTERVAL 15 MINUTE).

Then have a @Scheduled method in our Boot application run every minute to check if there are incoming products where the timestamp column is < now() and insert them as products and delete the corresponding incoming-products record.

Upvotes: 0

abhinav3414
abhinav3414

Reputation: 967

You can make use of both the functionalities you have mentioned.

First its good to have a lastUpdated field in tables which would help you in future also with other functionalities.

And then you can have an internal cache (map which has time and object reference), store objects in that and restrict editing for them. You can run a scheduler to check every minute and clear objects from you map and make them available for updating.

Upvotes: 0

yao Mr
yao Mr

Reputation: 79

you can make some check in your update method and delete method. If there are many methods, you can use AOP.

Upvotes: 0

Rick James
Rick James

Reputation: 142528

You should not use the locking available in InnoDB.

Instead, you should have some column in some table that controls the lock. It should probably be a TIMESTAMP so you can decide whether the 15 minutes has been used up.

If the 'expiration' and 'deletion' and triggered by some db action (attempt to use the item, etc), check it as part of that db action. The expiration check (and delete) should be part of the transaction that includes that action; this will use InnoDB locking, but only briefly.

If there is no such action, then use either a MySQL EVENT or an OS "cron job" to run around every few minutes to purge anything older than 15 minutes. (There will be a slight delay in purging, but that should not matter.

If you provide the possible SQL statements that might occur during the lifetime of the items, I may be able to be more specific.

Upvotes: 1

Related Questions