Table and partition locks (IXL) in non-TP database

Posted by Rob Fitzpatrick on 31-Oct-2017 11:36

DLC: 11.6.0 64-bit
OS: AIX 7.1

I'm querying _trans and _lock at a client site and seeing data I don't understand.  In an active transaction, I see multiple locks (so far no mystery).  But in addition to record locks, I see partition and table locks; all of the partition and table locks have flags intent exclusive and limbo.  The data looks like this:

Type                     RECID Flags       Table
-------- --------------------- ---------- ------
REC                   73465669 X   L         437
TAB                          0 IX  L         437
PART                         0 IX  L         452
REC                   73995436 X   L         477
REC                   74900888 X   L         433
TAB                          0 IX  L         433
TAB                          0 IX  L         452
TAB                          0 IX  L         480
TAB                          0 IX  L         477
REC                   74446879 X   L         445
TAB                          0 IX  L         445
REC                   74900883 X   L         433
REC                   74809996 X             198
PART                         0 IX  L         445
PART                         0 IX  L         477
REC                   74900887 X   L         433
PART                         0 IX  L         461
PART                         0 IX  L         433
REC                   74900886 X   L         433
TAB                          0 IX  L         421
REC                   74900880 X   L         433
TAB                          0 IX  L         461
REC                   74282016 X   L         452
PART                         0 IX  L         480
PART                         0 IX  L         421
PART                         0 IX  L         458
REC                   74900885 X   L         433
REC                   74907916 X   L         461
REC                   74900889 X   L         433
REC                   73497252 X   L         421
REC                   74900881 X   L         433
TAB                          0 IX  L         458
REC                   74900882 X   L         433
PART                         0 IX  L         437
REC                   74108513 X   L         480
REC                   74900884 X   L         433
REC                    5515976 X              49
REC                   74900890 X   L         433
REC                   74671320 X   L         458

So my questions:

  • Why am I seeing table locks?  It is my understanding that you can't take table locks from ABL.
  • Why am I seeing partition locks?  This is not a partitioned or MT database. 

Thanks.

Posted by Richard Banville on 03-Nov-2017 16:34

This is expected behavior when deleting a record.

Posted by dbeavon on 03-Nov-2017 16:39

As you imply, ABL isn't able to take regular shared/exclusive table locks, unlike SQL92 clients.  

But ABL must *communicate* with any SQL92 clients using intent locks (whether or not there are any actual SQL92 connections to the database is another matter).  This communication allows for more advanced transaction isolation options that exist in SQL but not in ABL.  For example a SQL client is able to operate in the "serializable" isolation level which essentially prevents a client from ever observing the effects of any other updates by any other clients for the entire duration of the transaction (ie. the SQL client essentially thinks it has an entire database all to itself).

Here is some info about standard isolation levels that are available in OE/SQL (stuff that is not specific to Progress/OE): knowledgebase.progress.com/.../20255

The "intent" locks in ABL will conflict with the types of locks that SQL92 takes in order to implement "serializable" isolation.  Here is more information about those conflicts: knowledgebase.progress.com/.../P37194

Is there any particular problem that you are trying to solve?  We've been seeing those IX locks in promon for years.  They shouldn't be the underlying cause for problems (blocking) that arises between ABL clients in the same database.

David

PS. The first KB article (20255) claims that "range locks" are possible in the OE database.  But in practice all I see with "serializable" is that OE (on the SQL side) takes full table locks (shared and exclusive).   But you don't have to worry about this if you have no SQL clients, let alone SQL clients using "serializable" isolation.

All Replies

Posted by Rob Fitzpatrick on 03-Nov-2017 15:15

Anyone?

Posted by Richard Banville on 03-Nov-2017 16:34

This is expected behavior when deleting a record.

Posted by dbeavon on 03-Nov-2017 16:39

As you imply, ABL isn't able to take regular shared/exclusive table locks, unlike SQL92 clients.  

But ABL must *communicate* with any SQL92 clients using intent locks (whether or not there are any actual SQL92 connections to the database is another matter).  This communication allows for more advanced transaction isolation options that exist in SQL but not in ABL.  For example a SQL client is able to operate in the "serializable" isolation level which essentially prevents a client from ever observing the effects of any other updates by any other clients for the entire duration of the transaction (ie. the SQL client essentially thinks it has an entire database all to itself).

Here is some info about standard isolation levels that are available in OE/SQL (stuff that is not specific to Progress/OE): knowledgebase.progress.com/.../20255

The "intent" locks in ABL will conflict with the types of locks that SQL92 takes in order to implement "serializable" isolation.  Here is more information about those conflicts: knowledgebase.progress.com/.../P37194

Is there any particular problem that you are trying to solve?  We've been seeing those IX locks in promon for years.  They shouldn't be the underlying cause for problems (blocking) that arises between ABL clients in the same database.

David

PS. The first KB article (20255) claims that "range locks" are possible in the OE database.  But in practice all I see with "serializable" is that OE (on the SQL side) takes full table locks (shared and exclusive).   But you don't have to worry about this if you have no SQL clients, let alone SQL clients using "serializable" isolation.

Posted by Rob Fitzpatrick on 03-Nov-2017 16:49

Thanks Rich and David for your responses and the links.

These partition and table locks, in and of themselves, aren't a problem I'm trying to solve.  I'm collecting data and documenting an application problem (related to transaction scoping) and was trying to reconcile what I was seeing in promon.  Thanks for your help.

This thread is closed