Informix Management: Informix performance Lock and concurrency

Improve concurrency and locks, allowing more users to access data faster.
From IBM Database Magazine Chinese version.

One important, but often overlooked, performance tuning step is to check how Informix Dynamic Server (IDS) handles locks and concurrency. In the Advanced Informix Performance Tuning course that I teach, tuning locks is one of the five important steps to improve performance.

Number of Locks

When Informix starts, it reads the ONCONFIG file and uses the lock parameter to create a memory structure (which we call the lock table) to manage the lock. The default setting for versions prior to IDS 11 contains 2,000 locks with very few locks. In IDS 11, the default number of locks is 20,000-the number of locks is increased, but it is still missing for high-capacity systems.

Each user session that opens a database, table, or read, update row will generate a lock in the lock table. Opening a database generates a shared lock in the database to prevent others from deleting the database. Opening the table generates a shared lock in the table to avoid changing it when you use the table. In addition to dirty reads, a shared lock is placed on each row being read. And when you update, delete, or insert a row, additional locks are added to the index used by the row.

Here's an example: updating 1,000 rows with 3 indexes generates 1,000 row locks, 3,000 index locks, and 4,002 database and table locks. The number of locks will soon exceed the default lock-table structure in memory. Informix is able to dynamically increase the capacity of the lock table as needed. However, the extra space provided for the lock table is located in another location in shared memory, which results in a split lock table. If the lock table overflows more than once, it can greatly reduce the search speed.

To diagnose whether the lock table overflows, you need to view the output of the Onstat-k command. The number of times the lock table overflows can be seen at the end of the output. Figure 1 shows a lock table with an overflow of two times. The last line shows that there are currently 42,239 active locks, and the total number of locks is 80,000. In this example, I will change the lock parameter to 80,000 in the ONCONFIG file so that no lock-table overflow occurs. In my professor's Advanced Performance Tuning course, when benchmarking was performed, we found that, in the case of default values, there were times when the overflow occurred to 30~40 times. This explains why modifying this setting can be included in the first five bits of the performance tuning setting.

Note: The ONSTAT-K option displays all active locks, so the display may be very long. If a large number of locks are defined in the ONCONFIG file and there are many users, the command outputs thousands of lines of content.

Back to the top of the page

Lock attribution

How to find out which user has used a lock on an object. The "owner" column in Figure 1 lists the addresses of users who have locks in shared memory. Use Onstat-u to view all users and identify the owner's user name by contrasting it with the address bar.

Figure 1. The onstat-k command displays the locked table in memory

Back to the top of the page

Locked table

How to find out which object is locked. The "tblsnum" column gives a locked table. Compare this table to the output of the following SQL statement, and convert the table's partnum to 16 to find out which table is locked.

Select TabName, Hex (partnum) Tblsnum from Systables where TabID > 99;

This SQL statement returns a list of tables and related Tblsnum. The example shown in Figure 2 tells you how to identify which table is locked.

Figure 2. Identify which table is locked.

Tblsnum 100002 has a special meaning-it shows that this is a database lock. Each user will create a shared lock on the database when the database is opened. Figure 1 shows the 3 shared locks.

Back to the top of the page

Lock level

Informix locks objects at these levels, such as databases, tables, pages, rows, bytes, and index keys. The Onstat-k command allows you to view the table space, row ID, and key/byte columns to identify the level of the lock. Table 1 lists the lock levels and how to identify them.

Table 1. Lock level description

Back to the top of the page

Lock type

The column "type" of the onstat-k command output (called "flags" in previous releases) describes a valid lock type. Table 2 lists the lock types.

Table 2. Description of the lock type shown in the Onstat-k output

Concurrency: Allows more users to share data

Concurrency is primarily about how to get more users to access and process the same data without locking each other out. The simple way to block all users by locking is to lock the entire database in exclusive mode. However, this approach is not suitable for use in high-volume, multiuser environments.

Informix provides the following five levels of concurrency, which are set by the Set isolation SQL command:

Dirty Read. This concurrency level does not lock any rows and reads rows that are locked or are being changed by other users. It can return uncommitted data that may be rolled back. This concurrency level is useful for environments where the data warehouse environment or for obtaining data is more important than reading committed records.

committed Read. This level does not lock any rows, but it fails if someone performs an update or uses an exclusive lock on a row. It reads only rows that have been committed. A row may also change after it is read, but it cannot have a lock that can be read. This level is the default setting for databases that use logs, and most OLTP uses that level. However, when a user requests a row that is locked by another user, you must provide error handling.

Cursor stability. This level places a shared lock on the selected line, so that when a user is reading a row, other users cannot update it. The lock is released when a second row is fetched or the pointer is closed.

repeatable Read. This level creates the maximum number of locks because it places a shared lock on each read row or user-scanned row so that the rows cannot be changed again, and repeated reads return the same records and values. These locks are freed after the transaction commits or rolls back. This level is the default setting for the database in ANSI mode.

Last committed Read. This level is a new feature in IDS 11 that works like committed read; However, when a row is locked to update, IDS reads the most recently committed record from the log. This level is valid only when a table is created with row-level locks, but it can significantly reduce lock errors and return the most recent valid data.

Back to the top of the page

Performance effects

Here's an example showing how concurrency and isolation levels affect locks and performance. In Figure 3, the user locks the record with an UPDATE statement. Figure 4 shows the results of 3 SQL statements trying to read the locked row. The first statement uses committed Read but fails, and the second statement gets it when the data is changed. However, the data is rolled back or changed again before the lock is released. The last statement reads it when the line is last committed, does not place any locks, and obtains valid data.

Figure 3. Lock column


Figure 4. Effects of concurrency and locks

Back to the top of the page

Read more data at a faster rate

See how many locks your system uses and understand the new isolation level last committed Read. Lock tuning improves user access to data, and allows more users to access data by choosing the correct isolation settings.