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

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

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).

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

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)