[Info-ingres] Triggers and locks

[Roy Hann]

Fabrizio Di Renzo wrote:

> Hi all, we have over 2000 tables for which we want to register all insert,
> update and delete operations.  For this reason we have created rules that
> insert a row in a table that is used to store the operation performed.
> 
> These are the rules
 
[snip]
 
> This is the procedure
> 
> create procedure  prd_spider_update (
>    cd_cliente VARCHAR(5) NOT NULL, cd_doc VARCHAR(3) NOT NULL, orario
>    VARCHAR(2) NOT NULL, nm_faldone INTEGER NOT NULL, nm_pratica INTEGER
>    NOT NULL, gr_pratica INTEGER NOT NULL, tp_operazione INTEGER NOT NULL
> ) AS begin
> 	 INSERT INTO spider (cd_cliente, cd_doc ,nm_faldone, nm_pratica,
> 	 gr_pratica, tp_operazione, tm_archiviaz)
> 	   SELECT :cd_cliente, :cd_doc, :nm_faldone, :nm_pratica,
> 	   :gr_pratica, :tp_operazione, CURRENT_TIMESTAMP()
> end
> 
> In this way, cuncurrency is generated on the table spider which causes
> locks on the database.	There is a way to force a row-level lock on the
> table spider?

There is no way to turn on row-level locking in the DDL, if that is what
you are hoping for.

You can use SET LOCKMODE ON tablename WHERE LEVEL=ROW in every application. 
Setting it using ING_SET (and ingsetenv) might make that a bit easier to 
apply, but not everything respects ING_SET; it depends on what languages
your applications are written in. 

There may be other ways to tackle the problem. You could consider using 
auditdb to trawl the journal files and write an application to insert the
relevant output to a table. The application would not block itself. 

Or maybe instead of inserting into spider in the prd_spider_update procedure, 
raise an event and attach the arguments as a message. Then write a listener
for the events that would insert into spider, similar to the suggestion
above. (This would have the disadvantage that it could have already raised 
an event when a subsequent rollback occurs.)

Or you could consider using HVR, but that costs money and there's a 
learning curve.

(I haven't mentioned using MVCC because I assume your application is
using locking for consistency control. MVCC is far preferable to normal
row-level locking but the testing burden would probably be prohibitive
for a large existing application. You _can_ turn on MVCC for individual
tables, but it is probably imprudent to mix locking and MVCC so I won't
suggest it.)

Roy