Session limit locks / limite de locks por sessão

This article is written in English and Portuguese (original version here)
Este artigo está escrito em Inglês e Português (versão original aqui)


English version:

In a very recent article I was complaining about the fact that we have some non documented features, and that from time to time those "leak" into the community. It just happened again, and this time it was the exact example I mentioned in that article. As I mentioned before, this one was already mentioned in a 2011 presentation in IOD. Now it happened in the IIUG mailing list (more precisely in the mailing list that acts as a gateway to the newsgroup comp.databases.informix), but we can also find it in a similar presentation made in IIUG 2011 conference which is available to IIUG members in their site.

I'm talking about an ONCONFIG parameter called SESSION_LIMIT_LOCKS. And yes, the name is self explanatory... It defines the maximum number of locks a session can use. Using it can be the best way to avoid a misbehaved session (or user error) to have impact on other sessions or the whole system. Since version 10 I believe (or maybe 9.4) we are able to extend the lock table. That would be a great idea but in fact it never provided the desired result. The problem was not that it doesn't work, but usually what happens is that a user does a mistake like loading millions of records into a table without locking the table, or they forget some condition in a WHERE clause of an UPDATE or DELETE instruction. So, it means that it usually won't stop after a few hundred or thousand more locks. It takes several lock table extensions, and this may consume a lot of memory. So usually the end result is one of these:

1. The user session ends up in a long transaction (very large - exceeding LTXHWM) with a very slow rollback
2. The system consumes a lot of memory with the abnormal lock table expansion, and the engine may end up hitting the SHMTOTAL memory limit, or overloading the machine with memory usage and consequently with swapping

Either case, it's not good.
I know about this functionality since 2011 (11.70 but I'm not sure about the fixpack) but I was under the impression that it was not fully functional. I did some tests (showed below) on 12.10.xC3 and I couldn't find any issue with it. But please consider that if it's undocumented, you won't be able to complain if it fails... In fact, I present here the tests for version 12.10.xC3. Previous versions may have different (wrong) behavior. Use it at your own risk. You will not get support!

So, let's try it:

• I have an instance with 20000 LOCKS:
  

  
  castelo@primary:informix-> onstat -c | grep "^LOCKS "
  LOCKS 20000
  castelo@primary:informix-> 
  
  

• I create a very simple test case that will consume 600 locks:
  

  
  castelo@primary:informix-> cat test_locks.sql
  DROP TABLE IF EXISTS test_locks;
  
  CREATE TABLE test_locks
  (
          col1 INTEGER
  ) LOCK MODE ROW;
  
  INSERT INTO test_locks
  SELECT LEVEL FROM sysmaster:sysdual CONNECT BY lEVEL < 601;
  castelo@primary:informix->
  
  

To start,  let's see how the engine is setup by default:

castelo@primary:informix-> echo "SELECT * FROM syscfgtab WHERE cf_name = 'SESSION_LIMIT_LOCKS'" | dbaccess sysmaster

Database selected.




cf_id         88
cf_name       SESSION_LIMIT_LOCKS
cf_flags      36928
cf_original   
cf_effective  2147483647
cf_default    2147483647

1 row(s) retrieved.



Database closed.

castelo@primary:informix->
 

So apparently by default it comes with (2^32) - 1 locks per session (or unlimited).
From several sources (IUG mailing list and IIUG 2011 conference presentation) we can assume this can be setup as an ONCONFIG parameter and a session variable. So let's start by trying to change the $ONCONFIG parameter:

castelo@primary:informix-> onmode -wm SESSION_LIMIT_LOCKS=100
SESSION_LIMIT_LOCKS is already set to 2147483647.
castelo@primary:informix-> 

Ops.... strange message... I tried other values and found the minimum value accepted seems to be 500:

castelo@primary:informix-> onmode -wm SESSION_LIMIT_LOCKS=500
Value of SESSION_LIMIT_LOCKS has been changed to 500.
castelo@primary:informix->

Let's verify:

castelo@primary:informix-> echo "SELECT * FROM syscfgtab WHERE cf_name = 'SESSION_LIMIT_LOCKS'" | dbaccess sysmaster

Database selected.




cf_id         88
cf_name       SESSION_LIMIT_LOCKS
cf_flags      36928
cf_original   
cf_effective  500
cf_default    2147483647

1 row(s) retrieved.



Database closed.

castelo@primary:informix->

Now we can try the test case and see what happens:

castelo@primary:informix-> dbaccess stores test_locks.sql

Database selected.


Table dropped.


Table created.


  271: Could not insert new row into the table.

  134: ISAM error: no more locks
Error in line 9
Near character position 56

Database closed.

castelo@primary:informix->

Great! If we limit to 500 locks, we cannot consume 600. That's a dream come true!
Furthermore, we can see it in online.log:

castelo@primary:informix-> onstat -m

IBM Informix Dynamic Server Version 12.10.FC3 -- On-Line -- Up 00:11:59 -- 287724 Kbytes

Message Log File: /usr/informix/logs/castelo.log

[...]

14:16:29  Maximum server connections 1 
14:16:29  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 7, Llog used 2

14:16:34  Value of SESSION_LIMIT_LOCKS has been changed to 500.
14:16:39  Session SID=42 User UID=1002 NAME=informix PID=27743 has exceeded the session limit of 500 locks.

castelo@primary:informix->

Great! What else could we ask for?
Continuing with the tests... In the presentation it's also suggested that we can SET ENVIRONMENT... So:

castelo@primary:informix-> dbaccess stores <<EOF
> SET ENVIRONMENT SESSION_LIMIT_LOCKS "1000";
> EOF

Database selected.


19840: Invalid session environment variable.
Error in line 1
Near character position 41


Database closed.

castelo@primary:informix->

Ouch... it doesn't recognize the variable name... Can it be one of the reasons why it's not documented? Could be... but if we think about it, the latest session environment variables all start with IFX_ prefix and then have the $ONCONFIG parameter. So I tried with:

castelo@primary:informix-> dbaccess stores <<EOF
> SET ENVIRONMENT IFX_SESSION_LIMIT_LOCKS "1000";
> EOF

Database selected.


Environment set.



Database closed.

castelo@primary:informix->

Good! So if I add this to the test case:

castelo@primary:informix-> cat test_locks_1000.sql
DROP TABLE IF EXISTS test_locks;

CREATE TABLE test_locks
(
        col1 INTEGER
) LOCK MODE ROW;

SET ENVIRONMENT IFX_SESSION_LIMIT_LOCKS "1000";
INSERT INTO test_locks
SELECT LEVEL FROM sysmaster:sysdual CONNECT BY lEVEL < 601;
castelo@primary:informix-> dbaccess stores test_locks_1000.sql

Database selected.


Table dropped.


Table created.


Environment set.


600 row(s) inserted.


Database closed.

castelo@primary:informix->

So, this does not means it works. But the above tests had the expected result. This is the best example of the situation I described in the previous article. Resources were used to implement this and it's not currently officially available to customers, although it was previously made public at least in three situations. Hopefully this will be documented soon. I feel this is one of the most wanted features in the customer's environment. Again, this would be a nice topic to be raised at IIUG conference in Miami


Versão Portuguesa:

Num artigo muito recente queixava-me do facto de termos funcionalidades não documentadas, e que ocasionalmente informação sobre essas funcionalidades "transparecia" para a comunidade. Ora isso acabou de acontecer, e desta feita exatamente com um dos exemplos que referia no artigo. Como escrevi na altura, isto já tinha sido apresentado numa conferência IOD de 2011. Desta vez foi referido na lista de correio do IIUG (mais precisamente na lista que serve de gateway com o newsgroup comp.databases.informix), mas também é possível encontrar uma referência ao mesmo tema numa apresentação da conferência do IIUG de 2011, estando essa apresentação disponível no site do IIUG na sua área reservada a membros.

Estou a falar de um parâmetro do ONCONFIG chamado SESSION_LIMIT_LOCKS. E sim, o seu nome diz tudo... define um máximo de locks que cada sessão pode usar. Usá-lo poderá ser a melhor forma de evitar que uma sessão "mal comportada" (ou um erro de utilização) tenha impacto nas outras sessões ou mesmo no sistema em geral.
Desde a versão 10 segundo creio (ou será 9.40?) que podemos expandir a tabela de locks. Isso seria uma excelente ideia, mas na verdade julgo que nunca teve o resultado planeado. O problema não está no facto de isso não funcionar, mas antes porque habitualmente o que acontece é alguém cometer um erro como carregar milhões de registos numa tabela sem a bloquear, ou esquecer uma condição numa cláusula WHERE de um UPDATE ou DELETE. Portanto, na prática o erro deixa de abortar por falta de locks (o Informix vai expandindo a tabela) ao fim de umas centenas ou milhares de registos. O resultado passa então a ser um destes:

1. A sessão do utilizador acaba por gerar uma transação longa (muito grande - excedendo o LTXHWM) e força um rollback geralmente demorado
2. O sistema consome muita memória devido às expansões anormais da tabela de locks, e isso pode levar o motor a bater no limite definido pelo SHMTOTAL, ou acaba por sobrecarregar a memória da máquina e eventualmente força o sistema a entrar em swapping

Em qualquer dos casos, o resultado não é bom!
Eu tenho conhecimento desta funcionalidade sensivelmente desde 2011, salvo erro num fixpack da 11.7, mas tinha a sensação que não estava funcional. No entanto fiz alguns testes (ver abaixo) na versão 12.10.xC3 e não consegui encontrar qualquer problema. Mas tenha em conta que algo não documentado é algo sobre o qual não se poderá queixar... Na verdade, apresentarei testes com a versão 12.10.xC3. Outras versões poderão ter comportamentos diferentes (errados). Use por sua conta e risco. Não terá suporte!

Vamos tentar então:

• Tenho uma instância com 20000 LOCKS:
  

  
  castelo@primary:informix-> onstat -c | grep "^LOCKS "
  LOCKS 20000
  castelo@primary:informix-> 
  
  

• Criei um caso de teste muito simples que ao ser executado vai consumir cerca de 600 locks:
  

  
  castelo@primary:informix-> cat test_locks.sql
  DROP TABLE IF EXISTS test_locks;
  
  CREATE TABLE test_locks
  (
          col1 INTEGER
  ) LOCK MODE ROW;
  
  INSERT INTO test_locks
  SELECT LEVEL FROM sysmaster:sysdual CONNECT BY lEVEL < 601;
  castelo@primary:informix->
  
  

Vamos começar por ver qual é a configuração do motor por omissão:

castelo@primary:informix-> echo "SELECT * FROM syscfgtab WHERE cf_name = 'SESSION_LIMIT_LOCKS'" | dbaccess sysmaster

Database selected.




cf_id         88
cf_name       SESSION_LIMIT_LOCKS
cf_flags      36928
cf_original   
cf_effective  2147483647
cf_default    2147483647

1 row(s) retrieved.



Database closed.

castelo@primary:informix->
 

Aparentemente, a pré-configuração são (2^32) - 1 locks por sessão (ou ilimitado).
Através de várias fontes (lista de correio do IIUG e apresentação feita na conferência do IIUG de 2011), podemos assumir que a´configuração pode ser feita por parâmetro do ONCONFIG e variável de sessão. Vamos então começar por mudar o parâmetro do ONCONFIG:

castelo@primary:informix-> onmode -wm SESSION_LIMIT_LOCKS=100
SESSION_LIMIT_LOCKS is already set to 2147483647.
castelo@primary:informix-> 

Ops.... mensagem estranha... Mas eu tentei outros valores e aparentemente o mínimo que podemos definir são 500 locks:

castelo@primary:informix-> onmode -wm SESSION_LIMIT_LOCKS=500
Value of SESSION_LIMIT_LOCKS has been changed to 500.
castelo@primary:informix->

Vamos verificar:

castelo@primary:informix-> echo "SELECT * FROM syscfgtab WHERE cf_name = 'SESSION_LIMIT_LOCKS'" | dbaccess sysmaster

Database selected.




cf_id         88
cf_name       SESSION_LIMIT_LOCKS
cf_flags      36928
cf_original   
cf_effective  500
cf_default    2147483647

1 row(s) retrieved.



Database closed.

castelo@primary:informix->

Parece bem. Agora podemos tentar o caso de teste que deveria exceder os locks permitidos e ver o que acontece:

castelo@primary:informix-> dbaccess stores test_locks.sql

Database selected.


Table dropped.


Table created.


  271: Could not insert new row into the table.

  134: ISAM error: no more locks
Error in line 9
Near character position 56

Database closed.

castelo@primary:informix->

Excelente! Se definimos 500, não podemos consumir 600. Parece um sonho tornado realidade!
Mas mais ainda, podemos ver isto no online.log:

castelo@primary:informix-> onstat -m

IBM Informix Dynamic Server Version 12.10.FC3 -- On-Line -- Up 00:11:59 -- 287724 Kbytes

Message Log File: /usr/informix/logs/castelo.log

[...]

14:16:29  Maximum server connections 1 
14:16:29  Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 7, Llog used 2

14:16:34  Value of SESSION_LIMIT_LOCKS has been changed to 500.
14:16:39  Session SID=42 User UID=1002 NAME=informix PID=27743 has exceeded the session limit of 500 locks.

castelo@primary:informix->

Ótimo! O que poderíamos pedir mais?!
Continuando com os testes... Na referida apresentação é sugerido que podemos usar a instrução SET ENVIRONMENT... Portanto:

castelo@primary:informix-> dbaccess stores <<EOF
> SET ENVIRONMENT SESSION_LIMIT_LOCKS "1000";
> EOF

Database selected.


19840: Invalid session environment variable.
Error in line 1
Near character position 41


Database closed.

castelo@primary:informix->

Ouch... Não reconhece o nome da variável... Poderá ser uma das razões porque ainda não está documentado? Talvez... mas se pensarmos um pouco, as últimas variáveis de sessão que têm sido introduzidas, todas começam com o prefixo "IFX_" e depois têm o nome do parâmetro equivalente no ONCONFIG. Assim sendo, tentei isto:

castelo@primary:informix-> dbaccess stores <<EOF
> SET ENVIRONMENT IFX_SESSION_LIMIT_LOCKS "1000";
> EOF

Database selected.


Environment set.

Database closed.

castelo@primary:informix->

Boa! Depois adicionei isto ao caso de teste:

castelo@primary:informix-> cat test_locks_1000.sql
DROP TABLE IF EXISTS test_locks;

CREATE TABLE test_locks
(
        col1 INTEGER
) LOCK MODE ROW;

SET ENVIRONMENT IFX_SESSION_LIMIT_LOCKS "1000";
INSERT INTO test_locks
SELECT LEVEL FROM sysmaster:sysdual CONNECT BY lEVEL < 601;
castelo@primary:informix-> dbaccess stores test_locks_1000.sql

Database selected.


Table dropped.


Table created.


Environment set.


600 row(s) inserted.


Database closed.

castelo@primary:informix->

Bom, nada disto garante que funcione. Mas os testes acima tiveram o resultado esperado. Isto é o melhor exemplo da situação que descrevi no artigo já referido. Foram consumidos recursos para implementar isto, mas não está oficialmente disponível para os clientes, embora já tenho sido referido publicamente três vezes antes. Esperemos que isto seja documentado em breve. No meu entender esta é uma das funcionalidades mais desejadas pelos clientes. Refiro novamente que este seria um bom tópico de discussão na conferência de  utilizadores que se avizinha em Miami