Triggers and locks

[Fabrizio Di Renzo]

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

create rule spider_insert_tab_01 AFTER INSERT ON tab_01 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 1);
create rule spider_insert_tab_01 AFTER DELETE ON tab_01 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 2);
create rule spider_insert_tab_01 AFTER UPDATE ON tab_01 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 3);

create rule spider_insert_tab_02 AFTER INSERT ON tab_02 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 1);
create rule spider_insert_tab_02 AFTER DELETE ON tab_02 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 2);
create rule spider_insert_tab_02 AFTER UPDATE ON tab_02 EXECUTE PROCEDURE prd_spider_update (cd_cliente = old.cd_cliente, cd_doc = old.cd_doc, nm_faldone = old.nm_faldone, nm_pratica = old.nm_pratica, gr_pratica = old.gr_pratica, tp_operazione = 3);

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?

Fabrizio

[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

[G Jones]

Perhaps partitioning the 'spider' table would help, so your concurrent rule-fired inserts are acting on multiple physical tables rather than just one.
(It's a less effective approach if spider has secondary indexes though, those can't be partitioned so a single index spans all partitions of the base table).

[Paul White]

Ciao Fabrizio,

Roy's suggestion of extracting audit journals is a fine idea. I keep 12 months of journals for auditing and offline analysis - and sometimes for recovery.

If you considered making use of a DBevent with a separate monitoring program, such as OpenROAD or ESQL/C, it could inspect the parameters to decide to write to different audit tables or write to a different database or could output to text file or some other system service.

I have given some excerpts below for ESQL/C. This code is rehashed from a production implementation which monitors changes to critical tables and listens for client DBevent request. It updates data, sends emails, kicks off print jobs and performs other important functions depending on various (customisable) rules (for example customer balance > credit_limit).

Something to keep in mind is that you will miss events if the monitoring program is not running.

== Schema ==
create dbevent spider_audit
\p\g
grant register on dbevent "ingres".spider_audit to public
\p\g
grant raise on dbevent "ingres".spider_audit to public
\p\g

create procedure prd_spider_update (
audittab VARCHAR(32) NOT NULL,

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

) =
DECLARE
Lv_EventText = varchar(2000) not null with default;
BEGIN
Lv_EventText = 'INSERT INTO ' + :audittab +
'(cd_cliente, cd_doc ,nm_faldone, nm_pratica, gr_pratica, tp_operazione, tm_archiviaz) ' +
' VALUES(' +
'''' + cd_cliente + ''',' +
'''' + cd_doc + ''',' +
'''' + orario + ''',' +
varchar(nm_faldone) + ',' +
varchar(nm_pratica) + ',' +
varchar(gr_pratica) + ',' +
varchar(tp_operazione) + ',' +
'''' + varchar(CURRENT_TIMESTAMP()) + ''')';

RAISE DBEVENT "ingres".spider_audit :Lv_EventText WITH SHARE;
END

\p\g


== esql/c program ==
...
EXEC SQL CONNECT :DBNAME IDENTIFIED BY spider_audit_service;
...
EXEC SQL register dbevent spider_audit;
...
while (1) {
EXEC SQL get dbevent with wait;
...
EXEC SQL inquire_sql (:evtext = dbeventtext);
...
if ( ! strncmp(evtext,"exit",4) ) {
printf ( "Exit command received");
break;
}
EXECUTE IMMEDIATE :evtext;
}

ps. your AFTER INSERT rule looks like it needs to refer to the new record rather than the old record?

Paul
&

[Fabrizio Di Renzo]

Thank you all,
we will try to use dbevent, I think this is a good idea.
We never used it, but it seem the better solution.

> ps. your AFTER INSERT rule looks like it needs to refer to the new record rather than the old record?

We need to refer to old record

Fabrizio

[Roy Hann]

Fabrizio Di Renzo wrote:

> Thank you all,
> we will try to use dbevent, I think this is a good idea.
> We never used it, but it seem the better solution.

Keep in mind that a raised event won't be "unraised" if there is a
rollback, so there is risk of recording things which--in a sense--didn't
happen.

Geraint's partitioning suggestion might be the quickest, easiest, and
most robust thing to try first. It requires no code and can be
backed out quickly if it doesn't help.

Roy (Kicking himself for forgetting to mention his own favorite solution)