Many times you might have a table in a database that holds items for processing. There may even be multiple steps between multiple people or tasks that access this same information at the same time. In fact, you might even have an entire workflow for processing items in a single table with a state column to indicate where in the workflow it is. The real question is how do you prevent these individuals or processes from accessing the same items at the same time.
Here is a lock mechanism that allows you to get a limited number of items at a time for processing without colliding with an identical request at the same time. First, set up the database table so that it has a column that can take a lock identifier. The lock identifer is NULL if the item is not locked or a value derived from SELECT NEWID() (SQL Server) or SELECT UUID() (MySQL).
The lock identifer is set on the rows or items to process with an update followed by a select on the rows with that same identifier. The following demonstrates how to lock a queue of 25 items to process:
Releasing the lock is done by selecting a sample row based on a primary key retrieved from the select statement used during the locking procedure to get the lock identifier. The lock is then released through an update.
Obviously, the SQL Server version is a bit more verbose and complex than MySQL. Some of the statements may be optimized or combined, but this gives the general idea.