Table Locking

By default, MSSQL doesn't support the type of record and table locking that COBOL expects. For this reason, the Connector implements its own locking method. This is accomplished with the addition of two tables to a database. You choose which database will hold these tables during installation of Database Connectors.

Before using the locking tables, you must execute the included ms_inst.sql script. (See the installation instructions you used from this manual for the exact procedure. You can find them in the Contents.) If you don't perform this step, the first time you try to execute a COBOL program that opens an MSSQL table, you will receive error 9D,11, " Lock Table Incorrect".

AcuLocks1 Table

The first locking table is called AcuLocks1; it holds the record locks. The columns in this table are the

  • DBID
  • Table ID
  • Process ID of the process holding the lock
  • Primary key of the record that is locked

There is a unique index on the DBID, the Table ID, and the Key Value, so that inserts into this table are automatically rejected if another user holds a lock on the row in question. This also gives the DBA the information needed to determine who has locks set, and whether the user in question still has a connection to the server.

AcuOpenTables1 Table

The second locking table is called AcuOpenTables1; it holds information about open tables.

The columns in this table are

  • The DBID
  • The Table ID
  • The process ID (PID) of the process that has the table open
  • The Open Mode (Input, Output, I/O or Extend)
  • Whether Multiple records can be locked
  • Whether the file can be open for I/O by any other users
  • Whether the file can be open at all by any other users
  • Whether Mass Update was specified in the open

There are no indices on this table, but there is a trigger, which will automatically reject opens that are not allowed based on other users' open modes.

By using these lock tables, Database Connectors is able to support all the types of locking ordinarily supported. No special configuration variables are required.

This method of locking is all that is needed if no applications other than COBOL programs are going to be using Database Connectors for MSSQL. But if your site has other applications that access the MSSQL databases, you must use a method of locking that is inherent to Microsoft SQL Server.

Another method of locking that Microsoft SQL Server supports internally is the result of time stamping and the use of BROWSE MODE (see the discussion of BROWSE MODE in the Microsoft SQL Server Commands Reference Manual). If a table has a timestamp column, the Connector will use browse mode. This will allow the server to detect whether another application has modified a record while it has had it locked.