Page 1 of 1

MySQL table(s) locking

Posted: Mon Aug 16, 2010 9:49 am
by alex.barylski
I have an application that is designed to build repair documents for the aviation industry. These documents serve as templates for work order's (which is the system I am building now). When you create a work order, you use a repair document as a template and once cloned, you tweak the W/O as required to repair the given part.

Obviously, data validity is critically important, so I am wondering what the best approach would be to prevent the W/O system from readin/cloning the repair document table(s) while they are being updated by their own management system?

Basically, our QA department uses another application to build and maintain these repair document templates. Then guys on the floor use a W/O management system to query these templates, which are cloned and then modified locally within the W/O system.

My fear is that, an engineer will approve changes to the repair document in the middle of a clone (unlikely but possible) by the W/O system. These documents are structly controlled, so if the W/O begins cloning a repair document and half way through the repair document is updated, it's revision number is bumped up and it's data/entities potentially changed. The resulting W/O cannot have half the old revision and half the new revision, for obvious reasons.

I need to essentially lock about 6 tables from reading, writing, etc. Until the repair document system has finished it's transaction.

Table locking and transactions do not seem to prevent reading, so I am cuious as to whether this can be changed or whether a custom solution is in order, such as keeping value in SHMOP and polling the value before executing a clone.

What is your experience and/or solutiuon in this situation?

Cheers,
Alex

Re: MySQL table(s) locking

Posted: Mon Aug 16, 2010 7:45 pm
by josh
Welcome to Enterprise programming. http://martinfowler.com/eaaCatalog/pess ... eLock.html 8)

When he says a 'business transaction' he means a "user sitting down trying to do some task", which will be represented by [presumably] an object encapsulating the concept of this "business transaction". Basically think of Dreamweavers FTP file check in & check out. If you check out a file I can't even check it out until you check it back in, or if you got hit by a bus & died, or got fired, maybe I want to edit the file, and could bypass the locking system and press "steal PCspectra's lock and checkout the file anyways"

Differs from an optimistic lock because an optimistic lock lets me waste time before it tells me I can't edit the file. SVN is optimistic, which entails some sort of conflict merging capability to not screw over one of the two users.

Code: Select all

if( !$businessTransaction->canAcquireLock() )
{
 echo 'some one else has the lock on this record set';
}
else
{
 $businessTransaction->acquireLock(); // you're just storing a token basically and enforcing it in PHP
 // do what you wanted to do
}
I use something similar on my cron scripts. Lets say I run a file hourly and one day it runs long, I make it so only one instance of each cron script can be running. That allows me to essentially emulate a daemon process by running something very frequently, without worrying if two processes run simultaneously. The only down side to that technique is if a cron script dies before releasing the lock, someone would have to manually expire that lock. Usually the locks are just a table. Some kind of key+token. The key could be an alphanumeric meaningful string, a user's ID, etc.. You can see this for example in my "socks" code base, where I developed a locking device for my cron scripts using TDD:

Example test code

Code: Select all

<?php
class PhpStats_CompactorTest_LockTest extends PhpStats_UnitTestCase
{
    function testAcquiresLock()
    {
        $compactor = new PhpStats_Compactor();
        $this->assertFalse( $compactor->hasLock() );
        $compactor->acquireLock();
        $this->assertTrue( $compactor->hasLock() );
    }
    
    function testAcquiresLockAndBlocksConcurrentCompacters()
    {
        $compactor1 = new PhpStats_Compactor();
        $compactor2 = new PhpStats_Compactor();
        $compactor1->acquireLock();
        $this->assertFalse( $compactor2->hasLock() );
    }
    
    function testFreesLock()
    {
        $compactor = new PhpStats_Compactor();
        $compactor->acquireLock();
        $compactor->freeLock();
        $this->assertFalse( $compactor->hasLock() );
    }
    
    /**
    * @expectedException Exception
    */
    function testWhenCantAcquireLockThrowsException()
    {
        $compactor1 = new PhpStats_Compactor();
        $compactor2 = new PhpStats_Compactor();
        $compactor1->acquireLock();
        $compactor2->acquireLock();
    }
    
    function testCompactingWithoutLockShouldAcquireLock()
    {
        return $this->markTestIncomplete();
    }
}
Example implementation:

Code: Select all

function hasLock()
    {
    	if( !$this->lockToken )
    	{
			return false;
    	}
		$select = $this->db()->select()
			->from( $this->table('lock') )
			->where( 'token = ?', $this->lockToken );
		$row = $select->query( Zend_Db::FETCH_ASSOC )->fetch();
		if( !$row )
		{
			return false;
		}
		if( $this->lockToken == $row['token'] )
		{
			return true;
		}
		return false;
    }
    
    function acquireLock()
    {
    	if( $this->hasLock() )
    	{
			return;
    	}
    	
    	$select = $this->db()->select()
			->from( $this->table('lock'), 'count(*)' );
		if( $select->query()->fetchColumn() )
		{
			$msg = 'Some one else has the lock';
			$this->log($msg);
			throw new Exception( $msg );
		}
			
    	$rand = md5(uniqid());
		$this->lockToken = substr( $rand, 0, 20 );
		$this->db()->insert( $this->table('lock'), array( 'token' => $this->lockToken ) );
    }
    
    function freeLock()
    {
    	$cond = sprintf('token=\'%s\'',$this->lockToken);
		$this->db()->delete( $this->table('lock'), $cond );
    }