Informix management: Informix performance lock and concurrency

Improve concurrency and locks so that more users can access data faster.
From the Chinese version of IBM database magazine.

A very important but often overlooked Performance Tuning step is to check how Informix Dynamic Server (IDS) handles locks and concurrency. In my advanced Informix Performance Tuning course, tuning locks are one of the five important steps to improve performance.

Number of locks

When Informix is started, it will read the onconfig file and use the lock parameter to create a memory structure (called the lock table) to manage the lock. In versions earlier than IDs 11, the default settings contain 2,000 locks, and the number of locks is very small. In IDs 11, the default number of locks is 20,000-the number of locks has increased, but it is still lacking for high-capacity systems.

Each user session that opens a database, table, or reads or updates rows generates a lock in the lock table. Opening the database generates a shared lock in the database to prevent others from deleting the database. Opening a table generates a shared lock in the table to avoid changing it when using the table. In addition to dirty reads, a shared lock is placed on each read row. When a row is updated, deleted, or inserted, an additional lock is added to the index used by the row.

Here is an example: Update 1,000 rows with three indexes to generate 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 the memory. Informix can dynamically increase the capacity of the lock table as needed. However, the extra space provided for the lock table is located at another location in the shared memory, which leads to the split of the lock table. If the lock table overflows multiple times, the search speed is greatly reduced.

To diagnose whether the lock table overflows, You need to view the output of the onstat-K Command. The number of overflow times of the lock table can be seen at the end of the output. Figure 1 shows a lock table that overflows twice. The last row 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 will occur. In my advanced performance tuning course, when performing a benchmark test, we found that when using the default value, sometimes overflow occurs as many as 30 ~ 40 times. This explains why modifying this setting can be included in the top five performance tuning settings.

Note: The onstat-K option displays all the active locks, so the displayed content may be very long. If a large number of locks are defined in the onconfig file and there are many users, this command will output thousands of lines of content.

 

Back to Top

Lock attribution

How can I find out which user has used a lock on an object? The "owner" column in Figure 1 lists the addresses of users with locks in the shared memory. Use onstat-u to view all users and compare them with the address column to identify the owner's username.

Figure 1. The onstat-K command displays the lock table in the memory

Back to Top

Locked table

How can I find out which object is locked? The "tblsnum" column shows the locked table. Compare the table with the output of the following SQL statement, and convert the partnum of the table to hexadecimal to find out which table is locked.

select tabname, hex(partnum) tblsnum from systables where tabid > 99;

 

This SQL statement returns a table list and related tblsnum. The example shown in Figure 2 shows how to identify which table is locked.

Figure 2. Identify which table is locked

Tblsnum 100002 has a special meaning-It indicates that this is a database lock. Each user will create a shared lock in the database when opening the database. Figure 1 shows three shared locks.

 

Back to Top

Lock level

Informix locks objects at the database, table, page, row, byte, and index key levels. You can use the onstat-K Command to view the tablespace, row ID, and key/byte column to identify the lock level. Table 1 lists the lock levels and how to identify them.

Table 1. Lock level description

Back to Top

Lock type

The "type" column output by the onstat-K Command (called the "flag" in previous releases) describes the valid lock types. Table 2 lists the lock types.

Table 2. Description of lock types displayed in onstat-K output

Concurrency: allows more users to share data

Concurrency is mainly about how to allow more users to access and process the same data without mutual exclusion through locks. The simple practice of rejecting all users by locking is to lock the entire database in exclusive mode. However, this method is not suitable for high-capacity, multi-user environments.

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

Dirty read.This concurrency level does not lock any row and reads the row that another user locks or is changing. It can return uncommitted data that may be rolled back. This concurrency level is useful for data warehouse environments or environments that are more important than reading submitted records.

Committed read. ThisNo row is locked, but it fails if someone executes an update or uses an exclusive lock on the row. It only reads committed rows. The row may change after it is read, but it cannot have a locks that can be read. This level is the default setting of the database that uses logs, and most OLTP uses this level. However, when a user request is locked by another user, you must provide error processing.

Cursor stability. ThisA shared lock is placed on the selected row. Therefore, when a user is reading a row, other users cannot update it. When another row is obtained or the pointer is closed, the lock is released.

Repeatable read.Create the maximum number of locks at this level because it places a shared lock for each read row or row scanned by the user so that these rows cannot be changed, repeated reading will return the same record and value. These locks are released after the transaction is committed or rolled back. This level is the default setting for ANSI databases.

Last committed read. This level is a new feature in IDS 11, which works very similar to committed read. However, when the lock is used to update a row, IDs will read the recently submitted records from the log. This level is valid only when row-level locks are used to create a table, but it can greatly reduce lock errors and return the latest valid data.

 

Back to Top

Performance

An example is provided to show how the concurrency and isolation level affect the lock and performance. In Figure 3, you use an update statement to lock the record. Figure 4 shows the result of three SQL statements trying to read the locked row. The first statement uses committed read, but fails; the second statement obtains it when changing data. However, the data will be rolled back or changed again before the lock is released. The last statement reads the row when it is submitted for the last time. It does not place any locks and obtains valid data.

Figure 3. Locking a column

Figure 4. Impact of concurrency and lock

Back to Top

Read more data at a faster speed

Check the number of locks used by your system and learn about the new isolation level last committed read. Through lock optimization, users can access data more quickly. By selecting the correct isolation settings, more users can access data.