Monday, March 19, 2007

SQL Lock

One of the mailing lists that I have been a member of, has been seeking assistance to the best approach in preventing 2 or more simultaneous access to a record from a database. One member suggested the use of LAST_INSERT_ID() (for MySLQ), another one suggested of using a global variable (auto-increment), and the inquirer has resorted to the use of transactions. Among the three solutions presented, I prefer on using transactions since for me it was the most reliable.

We all know that transactions can degrade performance as it creates an overhead for each request. So I came to a solution that uses transactions but with optimized performance. I based it on the SingleTon pattern. Here's what I presented to the group in pseudo-code:


id = (SELECT ID from Table1 where ID = Id_value and ProcessID = NULL)

if (id > 0)
{

 LOCK
{

 id = (SELECT ID from Table1 where ID = Id_value and ProcessID = NULL)
UPDATE Table1 SET ProcessID = (function to get the unique identifier) where ID = id

}


}

Now, we will examine the codes one by one.

The first step, "id = (SELECT ID from Table1 where ID = Id_value and ProcessID = NULL)," will try to fetch the record.

The second step, "if (id > 0)," will check if the record has already been selected. If the record has already been tagged as fetched, then the request will terminate immediately.

The third step, "LOCK," will try to execute the request one at a time. This step is very essential for programs running with threads. This is the only place where transactions should be used, and we save extra overhead by checking on step#2.

The fourth step, "id = (SELECT ID from Table1 where ID = Id_value and ProcessID = NULL)," would seem to be as a redundant statement, right? You might think that it's not necessary, but it is for multi threading. Let us say you ran 100 threads. The top 10 threads have entered the "if" statement since the initial value of ProcessID is NULL. Though 10 threads have entered the "if" statement, they're executed one by one inside the block. Here's where step#4 is of importance. It will double-check if the record was already taken from one of the 10 threads that have entered the "if" statement.

On the fifth step, "UPDATE Table1 SET ProcessID = (function to get the unique identifier) where ID = id," it will tag the record as unavailable.

No comments:

Post a Comment