Informix Dynamic Server (IDS) Logging

IDS knows below lock types:

  • Shared lock
  • Update lock
  • Exclusive lock
  • Intent lock

Locking granularity in IDS:

Granularity: It is the extent to which a system is broken down into small parts, either the system itself or its description or observation. It is the “extent to which a larger entity is subdivided. For example, a yard broken into inches has finer granularity than a yard broken into feet”

IDS allows to place locks on below objects:

  • Database locks
  • Table Locks
  • Page locks
  • Row locks
  • Index key locks

 ***

Share Lock:

Share locks could be placed on rows that don’t 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 is 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.

Example: Open two sessions with userid: informix

From session1: open a database, ex:phase1, in exclusive mode as below

Database phase1 exclusive;

From session2: Try to access the phase1 database as “dbaccess phase1”, then you will get an error.

Remember that, even though you are Informix user in both session, you are still not able to access from another session.

Intent Lock:

Intent locks are special kinds of locks. If, for example, a row is update, an exclusive lock is placed on the row and 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 Locks:

Databases could be locked explicitly in exclusive or shared mode.

Database share lock:

A share lock is automatically placed on the database as soon as you open the database. This ensures that no other session could place an exclusive lock on the database or drop the database.

Example: Type command “dbaccess phase1” from session1.

From session2, type “onstat –k”. This will display the following output:

address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz

8e73d978 0        92c7210c 0        HDR+S    100002   205         0

HDR+S: Share lock

Rowid: is the hexadecimal row ID of the row describing this database in table sysdatabases in the sysmaster database.

Databases and their hexadecimal row IDs could be selected with the following SQL query:

Database sysmaster;

Select name, hex(rowid) FROM sysdatabases;

Database exclusive lock:

An exclusive lock can be explicitly set on a database. Utilities like dbexport places exclusive lock on the database to be exported.

Example: database phase1 exclusive

Now, output from onstat –k:

address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz

8e73d978 0        92c7210c 0        HDR+X    100002   205         0

Here, you can see an exclusive lock (HDR + X) on the database with the hexadecimal rowid=205

Table Locks:

Like databases, tables could either be locked in exclusive or share mode.

Locking a table exclusive mode prevents anybody else from reading or changing data in the locked table. An exception to this are sessions running with an isolation level of dirty read (i.e, read uncommited). These sessions 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.

To place a shared lock, you need to be in transaction as below:

begin; lock table orders in share mode

Output from onstat –k:

——————————

address  wtlist   owner    lklist            type     tblsnum  rowid    key#/bsiz

8e73d978 0        92c7b46c 8e741078 HDR+S    10010d   0           0

You see a share lock (HDR+S) and a row ID of zero (rowid=0). A zero row ID represents a table lock. The tablespace number (tblsnum=10010d) conforms to the hexadecimal partition number of this table.

The affected table could be identified in two ways:

  • Oncheck command

Oncheck –pt 0x0010010d | more

  • Data dictionary query command

select tabname, dbinfo(“dbspace”, partnum), hex(partnum) from systables where hex(partnum) = “0x0010010D”

Notice that you need to put a 0x00 in front of the hexadecimal number gathered from the onstat –k output, and you need to convert the letters to uppercase (10010d à 0x0010010D) in order to find the table through the dictionary query.

If it is a fragmented table you have to query, the sysfragments dictionary table:

select st.tabname, dbinfo(“dbspace”, sf.partn, hex(sf.partn) from systables st, sysfragments sf where st.tabid = sf.tabid and sf.fragtype = “T” and hex(partnum) = “0x0010010D”

Table exclusive lock:

An exclusive lock can be explicitly set on a table or might be implicitly set by statements like alter table.

Example: begin; lock table orders in exclusive mode

Output from onstat –k:

——————————

address  wtlist   owner    lklist            type     tblsnum  rowid    key#/bsiz

8e73d978 0        92c7b46c 8e741078 HDR+X    10010d   0           0

Page or row locks:

The lock granularity (page or row) could be specified during the creation of a table  or later can be changedwith the alter table statement

Example1: create table t1 (c1 int) lock mode (row)

Example2: alter table t1 modify lock mode (page)

You can determine lock mode of a table using,

  • Oncheck utility

Oncheck –pt  database_name:owner_name.table_name

  • Dictionary query

SELECT tabname, locklevel FROM systables WHERE tabname=”table_name”

The following abbreviations for the locklevel are shown by IDS:

  • B – Views (Check the locklevel of underlying table)
  • P – Page locking
  • R – Row Locking

Page Locks:

Page locks are placed on a database page during the processing of a row if the table has been configured for page level locking.

Page locks are represented with a double zero (00) at the end of a row ID.

Row Lock:

Row locks are placed on a database page during the processing of a row if the table has been configured for row-level locking.

Identifying a locked row:

——————————–

Database phase1;

Set isolation to dirty read;

SELECT * FROM customer WHERE hex(rowid)=”0x0000001A”;

Index Key Locks

IDS also places locks on the index keys like data rows in order to protect those index keys. Key value locking guarantees that unique keys remain unique because it doesn’t allow a second transaction to insert a key with the same value until the transaction that deleted the unique key is committed.

Index key locks are identified by a K in the key#/bsiz column of the onstat –k output.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: