Record locking

From Achievo/ATK Wiki

Contents 1 Intro 2 Setting up the application 3 Implementing record locks on a node 4 The results of record locking 5 Conclusion 6 Related Topics

Intro

Before diving into the details of how row level locking is implemented within the ATK framework. I would like to briefly explain its purpose. Most applications written using the ATK framework are designed to be used by many users simultaneously. There are likely some sections of the database that are unique to each user and only modified by them. However, a larger part of the data is shared by all of the users. Because of the asynchronous nature of the interaction between the users and the data, there is no guarantee that only one user will try to edit a record (row) of a table at one time. If two users were to simultaneously open the same record for editing, the user who saved the record last would override the changes made by the first user. This would not be a pretty picture if you lost some important information from the database.

To prevent two users from changing the same record at the same time a method must be implemented either within code or in the database. Not all databases support row level locking directly and most of the ones that do implement it in a customized way. ATK uses code to update a table in the database that stores which records each user has opened for editing. This table is then used as a lookup to prevent two users from editing the same record. On a side note if you look at the source code of the framework you will see that all accesses to this table are surrounded by a lock on the table.

Setting up the application

The first step to implementing record locking with ATK is to create the table that stores the record locks. In the directory /atk/lock/db are two files that can be used to create the table in your database (install-mysql.sql, and install-oracle.sql). Choose the file that you need for your database. Below is a listing of the file for the MySQL database. As can be seen below this SQL statement creates a table named db_lock with nine fields. (On a side note my favorite tool for working with MySQL is phpMyAdmin).

  # Use this installation script only for the atkDbLock type!
  
  CREATE TABLE db_lock
  (
    lock_id BIGINT NOT NULL,
    lock_table VARCHAR(100) NOT NULL,
    lock_record VARCHAR(255) NOT NULL,
    lock_stamp DATETIME NOT NULL,
    lock_lease DATETIME NOT NULL,
    lock_lease_count BIGINT NOT NULL,
    user_id VARCHAR(50) NOT NULL,
    user_ip VARCHAR(50) NOT NULL,
    session_id VARCHAR(32) NOT NULL,
    PRIMARY KEY (lock_id, lock_table, lock_record)
  );

Next, open the file config.inc.php in the root of your application tree. Add the following line to enable db locks.

  //Setup the application to use row level locking using a database table
  
  $config_lock_type = "db";

This completes the steps required to enable your application to use row level locking.

Implementing record locks on a node

The ATK framework allows individual nodes to implement record locking. Each node that has been created can either implement the locking mechanism or not implement it. Personally I choose to implement locking on all of my nodes. If you only have one administrator for some tables you may not want to do this. It does add some overhead to the application. To implement the locking mechanism a simple flag is set on the node. The flag is NF_LOCK. Short for Node Flag Lock. Below is an example which uses the NF_LOCK flag. This was created by modifying an employee node from the ATK demo application. The file is named class.employee.inc and can be found in virtually all of the lessons.

  class employee extends atkNode
  {  
    function employee()
    {
      /**
       * Another flag is introduced. It is NF_LOCK, which is
       * short for Node Flag Lock. By setting this flag, row level
       * locking is implemented so that only one user can edit a record
       * at a time.
       */
      $this->atkNode("employee", NF_ADD_LINK|NF_LOCK);
  
      ...

This completes the process of setting up record locking on ATK nodes.

The results of record locking

Now that record locking has been implemented the user interface is changed to reflect that it is being used. The first noticeable change is a gold lock with a question mark in the upper left corner of each table implementing locks. This is a visual indication to the user that record locking is being used. Each row that has been opened for editing will also have a gold lock in its first column. To find out which user has a record locked use the tool tip that is presented when the mouse is hovered over top of the gold lock. All of this can be seen in the picture below.

Conclusion

No database is useful unless the integrity of the data can be assured. The creators of the ATK framework have made using row level locking an easy task to implement. To ensure the integrity of your future employment at your company, take advantage of it and use it wherever possible.

Related Topics

When working with the ATK framework it is easy to setup ManyToOne type relationships using a few keystrokes. This has been spelled out in the Pizza Guides. Don't forget about the referential integrity of the data within the database as well. The MySQL database allows relationships or foreign keys to be implemented on both MyISAM and InnoDB tables.

Other node flags can be found at the Node Flags page.