Informix Dynamic Server locking, Part 1: Understand locking behavior and analyze locking conflicts in IDS
Introduction Multiuser databases with hundreds or even thousands of users and batch processes trying to update data simultaneously need some mechanisms to protect the data and keep it consistent. Beside transaction logging, locking is the key feature to achieve this. However, locking often results in conflicts and wait situations. These are one of the most common problem areas a DBA is faced with during his daily work. Analyzing locking problems without the appropriate scripts in place is complex and error-prone.
This article explains IDS locking mechanisms and aids you in analyzing locking conflicts and lock wait situations.
Lock types IDS knows several different types of locks. Those types are as follows:
Shared lock Shared locks could be placed on rows that do not have an exclusive lock on it. Other users might place additional shared locks or update locks on the same row, but no other exclusive locks are allowed.
Update lock An update lock is a special kind of lock generated by a cursor that has been declared with the for update clause. Update locks could only be placed on a row that currently has no update or exclusive lock on it. Once an update lock has been placed on a row, it is promoted to an exclusive lock as soon as the row is updated.
Exclusive lock An exclusive lock can only be placed on rows that do not have any other kind of lock on it. Once an exclusive lock is placed on a row, no other locks can be placed on the same row. It is exclusively reserved for this database session.
Intent lock Intent locks are special kinds of locks. If, for example, a row is updated, an exclusive lock is placed on the row and an intent exclusive lock is placed on the table. The intent exclusive table lock ensures that no other session could place a share or exclusive lock on the table as long as individual rows in the table have been exclusively locked.
. . . • Database Exclusive Lock An exclusive lock can be explicitly set on a database. Utilities like dbexport might also place an exclusive lock on the database to be exported. o SQL Statement database stores_demo exclusive
Listing 3. Exclusive lock on a database Output from onstat -k: ---------------------- Locks address wtlist owner lklist type tblsnum rowid key#/bsiz 300583dc 0 400d63d8 0 HDR+X 100002 207 0
Here you see an exclusive lock (HDR+X) on the database with the hexadecimal rowid=207.
Table locks Like databases, tables could either be locked in exclusive or share mode. An exclusive lock prevents anybody else from reading or changing data in this table. An exception to this are sessions running with an isolation level of dirty read read uncommitted). They are still able to read (possible inconsistent) data from the exclusively locked table. Locking a table in share mode allows others to select data from this table but prevents data modifications.
Dirty read (ANSI: Read uncommitted) If you read data with this isolation level, you will not lock anything yourself and you will not be blocked by any existing locks from other users. However, you might read inconsistent data; data that has not been committed yet. Dirty read is the only possible isolation level for databases created without logging. • Informix SQL o set isolation to dirty read [retain update locks]
Most tables are configured to exclusively lock. So, after selecting your database in your SQL, you should add the below line:
set isolation to dirty read;