Database Queues

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:

SQL Server


	DECLARE @lockID varchar(100)

	SET @lockID = NEWID()

	UPDATE Table1 SET Lock = @lockID WHERE PK IN (SELECT TOP 25 PK FROM Table1 (ROWLOCK, UPDLOCK) WHERE ... ORDER BY DateCol)

	SELECT PK, Col1, Col2 WHERE Lock = @lockID

MySQL


	SET @lockID = UUID()

	UPDATE Table1 SET Lock = @lockID WHERE ... ORDER BY DateCol LIMIT 25

	SELECT PK, Col1, Col2 WHERE Lock = @lockID

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.

SQL Server


	DECLARE @lockID varchar(100)

	SELECT @lockID = Lock FROM Table1 WHERE PK = n

	UPDATE Table1 SET Lock = NULL WHERE Lock = @lockID

MySQL


	SELECT @lockID := Lock FROM Table1 WHERE PK = n

	UPDATE Table1 SET Lock = NULL WHERE Lock = @lockID

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.

Database Queues

OTHER ARTICLES