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:
Thanks.
This is expected behavior when deleting a record.
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.
Anyone?
This is expected behavior when deleting a record.
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.
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.