CICS - Updates by Multiple users

Question

If around 50 users are trying to update the same field in a DB2 table, how will CICS handle this? How is this handled in a COBOL DB2 Program?

Answer 1

Some care must be taken to mange data integrity when CICS pseudo conversational programs interact with DB/2 to update a database, especially when multiple CICS transactions may be attempting to read/update the same DB/2 table row at nearly the same time.

What follows is a very simplified explanation of a common design pattern for pseudo conversational CICS transactions (this is the most common type of interactive programming pattern for CICS). The answers to this question: what-are-the-advantages-of-pseudo-conversational-vs-conversational-cics-programm provides a short overview of how pseudo conversational cics works, you might want to review this before continuing...

Pseudo conversational CICS transactions are designed to pick up their current running state from a COMMAREA (a chunk of memory that persists between transactions). The saved state in the COMMAREA is used by the program to determine what needs to be done next. It then does it, determines what the next state should be, saves the new state and any data it may need to "remember" into the CICS COMMAREA and then it exits - releasing all resources, including any DB/2 locks that it is holding.

DB/2 locks are key to preserving database integrity in the face of multiple select/update/insert/delete transactions competing for the same database rows. The problem with pseudo conversational CICS is that these locks are lost between the time a transaction picks up its data from a table and the time it needs to update the table.

The states that a typical pseudo conversational CICS transaction might go through are something like:

• Initial - Start a fresh transaction, Ask user to input Key Data (set next state to Display)
• Display - Validate Key entered on Initial State. Retrieve related data from DB/2 and display it (set next state to Commit)
• Commit - Validate changed data from Display state and update DB/2 (set next state to Initial)

Notice that data are retrieved during Display. A read lock is taken by DB/2 at this time to protect against other transactions from updating the row as it is being read. Depending on how DB/2 and your DML are configured, this lock may only be active for the duration of the Display phase or just while the row is being retrieved. In either case, that lock is lost as soon as the program exits. When the program restarts again it will be in Commit state ready to execute the appropriate DB/2 update/insert DML to effect the requested changes to the database.

Since there are no persistent DB/2 locks held between these state transitions nothing prevents another transaction from accessing or changing data between the time the first transaction did its display and then its update. This can lead to serious data integrity issues. Notice that while a transaction is active, it may hold DB/2 locks that effectively prevent other transactions for either reading uncommitted updates or form updating the rows it is "looking at" - but it cannot hold these between invocations of the "conversation".

A common mechanism used by programmers to maintain data integrity over the course of a pseudo conversational CICS transaction is to have all transactions rely on a single common updatable resource to act as an update collision detector. For example if your database is managing data related to your customers, there is probably a common unique key used to identify any given customer. These unique keys are often just a number or short string (eg. Customer-Id). All rows in all tables of your customer database probably use this same common unique key to identify a specific customer (after all that is what a key is all about).

Create a TRANSACTION table that contains two columns, one column for the Customer-Id and another for some transaction unique identifier, this is commonly just a timestamp generated at the beginning of the transaction (could also be a task-id, it just needs to be unique to a transaction). Call this transaction identifier the TRANS-KEY. When the Display phase of a transaction is entered the transaction has the Customer-Id for the first time. It then updates the TRANSACTION table for this Customer-Id with its TRANS-KEY. Both are saved to the COMMAREA. Note that the update here is done based on Customer-Id only, it will replace the current TRANS-KEY with its own unique value. The Display phase is entered, user enters their changes and Commit phase is entered. At this point the transaction attempts to update the TRANSACTION table based its saved Customer-id and TRANS-KEY. If the update fails the transaction "knows" some other transaction has started working on the same customer (the other transaction changed the TRANS-KEY for the same Customer-id so the current transaction either suffers a timeout or row-not-found error). To preserve database integrity the current transaction must back out. It will display some sort of message to the user notifying them that a concurrent update has been requested and that it must abandon the requested updates. If, on the other hand, the TRANS-KEY has not changed for the given Customer-id, a database lock will have been taken preventing any other transaction from changing that row for the same Customer-Id. The current transaction may then update the database because it know no other transaction has looked at or updated the data between the time it first started to operate and the time it commited its updates.

The key to making all this work is that all transactions that update the Customer database use the same TRANSACTION table to detect concurrent update attempts.

The resource locking mechanism described here is commonly called "collision detection" and is just one of many possible ways to preserve database integrity over the course of a pseudo conversational CICS transaction. The key point is that DB/2 locks are taken and dropped several times over the course of the transaction and that it takes some "cooperation" between the DB/2 lock manager, CICS Syncpoint manager and the application itself to maintain database integrity when using pseudo conversational CICS.

Finally, the transaction that actually gets to update the database will be the last one to retrive data - so the moral of this story is to get in late and finish early!

The above discussion is not COBOL specific, it will be true for all programming languages, including COBOL, used to develop applicaions using DB/2 and psuedo conversational CICS.

Answer 2

From the IBM manual, How CICS connects to DB2:

A CICS® DB2® attachment facility is provided with CICS. The CICS DB2 attachment facility provides CICS applications with access to DB2 data while operating in the CICS environment. CICS applications, therefore, can access both DB2 data and CICS data. CICS coordinates recovery of both DB2 and CICS data if transaction or system failure occurs.

The CICS DB2 attachment facility creates an overall connection between CICS and DB2. CICS applications use this connection to issue commands and requests to DB2. The connection between CICS and DB2 can be created or terminated at any time, and CICS and DB2 can be started and stopped independently. You can name an individual DB2 subsystem to which CICS connects, or (if you have DB2 Version 7 or later) you can use the group attach facility to let DB2 choose any active member of a data-sharing group of DB2 subsystems for the connection. You also have the option of CICS automatically connecting and reconnecting to DB2. A DB2 system can be shared by several CICS systems, but each CICS system can be connected to only one DB2 subsystem at a time.

In plain English, think of CICS as having a multiple connection pool to DB2.

When a DB2 table is defined, it is defined with either page or row locking. (It can be defined with table locking, but this is rare). The DB2 database engine will not allow more than one user to update a page or a row at a time, depending on the locking level.

There are also read locks to maintain read consistency of tables. DB2 calls these read locks isolation levels.