I Think I'll Call Them "Transactions" was originally published on October 31, 2006.

As programmers, we derive satisfaction from our ability to create solutions to other peoples' problems. Unfortunately, the problems that we solve aren't the exciting "world-hunger" type, they're more along the lines of, "Sally is spending too much time doing expense reports and would like to automate the process." The inherently boring nature of these business problems lead many programmers to seek out new problems to solve, the most common of which is the meta-problem: a problem with the process of creating a solution for the actual problem.

The solutions to meta-problem take on many forms: NIH (Not Invented Here), IHBLRIA (Invented Here, But Let's Reinvent It Anyway), The Inner-Platform Effect, and so many others. Every once in a very rare while (as of yesterday, only seventeen times in the history of all software development), the existing development tools are not adequate in solving the immediate problem and the solution to the meta-problem is actually beneficial to the overall solution. Today's example, courtesy of Robert Rossney, is not one of the seventeen.

The original programmer behind a SQL Server-based financial application that Robert has come to maintain discovered a meta-problem in the development process: if two or more database operations occurred simultaneously, the results of those operations would be completely unpredictable. The operations were reading from and changing the same set of data, meaning that, when one operation a retrieved a value from the database, by the time it made a decision based on that value, the original value was changed by the other operation.

Now before all you database developers scream "Use Built-in Transactions!," let me remind you that there are a whole lot of problems associated with built-in database transactions. First and foremost, they're built-in and using them would give programmers one less meta-problem to solve which would increase the boring factor by at least three. Secondly, built-in transactions ... uh ... -- okay, so that's the only problem I can think of right now. But it was big enough for Robert's predecessor to develop his own transactional logic.

His transactional system was built on top of SQL Server 2000 and involved using a table called db__locks which had a row for each table in the database and contained the table's name and a lock level of 0 (not locked) or 1 (exclusively locked). Obviously, he had big expansion plans for the future. Code to wait, lock, and unlock is scattered across countless stored procedures. Following is an example of this code, from the start of the stored procedure that updates various tables based on what's in the General Ledger.

-- Wait for lock
WHILE @LockLevel <> '0'
  BEGIN
    SELECT @LockLevel = LockLevel
      FROM db__Locks
     WHERE LockTbl = 'GL_Operations'

    IF @LockLevel <> '0' WAITFOR DELAY '00:00:01'
  END

-- Set lock
UPDATE db__Locks
  SET LockLevel = '1'
  WHERE LockTbl = 'GL_Operations'

... snip whole bunch of code ...

-- Clear lock
UPDATE db__Locks
  SET LockLevel = '0'
  WHERE LockTbl = 'GL_Operations'

Fun fact: this transactional system comes with an admin utility to reset the db__Locks table if needed.

Another fun fact: on-call developers use this utility fairly often in the middle of the night when they're woken up by a support call

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!