One way is to use
SELECT FOR UPDATE
structure to lock the records during the select and release the locks upon
COMMIT
or
ROLLBACK
. For more information, see
MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.4 Locking Reads[
^]
If you need to extend the control outside the boundaries of a transaction you can use
TIMESTAMP
data type to implement optimistic concurrency control. About TIMESTAMP see
MySQL :: MySQL 8.0 Reference Manual :: 11.2.2 The DATE, DATETIME, and TIMESTAMP Types[
^]
For an example of optimistic concurrency you can refer to
How to Prevent When a Transaction Accidentally Overwrites Modified Data[
^]. Even though the article is written for SQL Server, the principle is the same.
EDIT
Looking at the question again I felt unsure if you're using MySQL or SQL Server. If you're using Microsoft SQL Server, you can lock the records you select for example using
WITH UPDLOCK
hint in your query to ensure that the records are locked regardless of the concurrency settings. See
Table Hints (Transact-SQL) - SQL Server | Microsoft Docs[
^]
And in case of SQL Server the article should be accurate.