Обсуждение: preventing deadlocks

Hi list!
My issue is as follows :
I have to do some  calculations based on *exact* number of rows in 2
tables (with a filter) meaning:
SELECT count(*) FROM a WHERE a.row1 in (1,2,3,4);
SELECT count(*) FROM b WHERE b.row1 in (1,2,3,4);
However i couldn't use the count(*) since it is too slow beacause of the
table size.
So,i created a trigger that on insert increments and on delete
decriments special "counter" table
that contains
\d counter
    Column    |         Type          | Modifiers
--------------+-----------------------+-----------
 counter_type | character varying(30) |                       ---- the
table name (a or b)
 ident        | numeric(10,0)         |
----the
 count        | integer
|                                       ----the count


The problem of course is the locking issues while changing a and b
tables.What i am doing now is to
select 1 from counter  where counter_type='a' and ident in (1,2,3,4) for
update;
select 1 from counter  where counter_type='b' and ident in (5,6,7) for
update;
Befor changing anything in tables "a" and "b"  in transaction .I am also
doing for update select on the
"a" and "b" tables itself ,that is:
select b from a  where pkey in (5,6,7) for update;

My problems:
[1] Is the for update lock anouth here?
[2] The "for update" queries  HAVE to be done in the same order in all
application which is pretty error prone -
it is very easy to forget in one place and get a deadlock.
[3] Can i make a trigger that automatically locks the counter_type='b'
if a for update select was done on table b?
something like (pseudo):
trigger on select for update table b{
    select for update where ident = OLD.pkey ;
}
[4] Can i combine queries for multiple tables to make locking atomic:
select 1 from b,counter where b.pkey in (1,2,3,4) and counter.ident in
(1,2,3,4);
Hope for help and sorry for long message.
evgeny

On Tue, Dec 27, 2005 at 11:48:55 +0200,
  Tsirkin Evgeny <tsurkin@mail.jct.ac.il> wrote:
>
> Hi list!
> My issue is as follows :
> I have to do some  calculations based on *exact* number of rows in 2
> tables (with a filter) meaning:
> SELECT count(*) FROM a WHERE a.row1 in (1,2,3,4);
> SELECT count(*) FROM b WHERE b.row1 in (1,2,3,4);
> However i couldn't use the count(*) since it is too slow beacause of the
> table size.

You should look through the past archives on this subject. There is a way to
do this that uses MVCC for incremental changes. You do want to make a sweep
through the delta table periodically which will need stronger locking, but
this won't block reads on a and b.

> So,i created a trigger that on insert increments and on delete
> decriments special "counter" table
> that contains
> \d counter
>    Column    |         Type          | Modifiers
> --------------+-----------------------+-----------
> counter_type | character varying(30) |                       ---- the
> table name (a or b)
> ident        | numeric(10,0)         |
> ----the
> count        | integer
> |                                       ----the count
>
>
> The problem of course is the locking issues while changing a and b
> tables.What i am doing now is to
> select 1 from counter  where counter_type='a' and ident in (1,2,3,4) for
> update;
> select 1 from counter  where counter_type='b' and ident in (5,6,7) for
> update;
> Befor changing anything in tables "a" and "b"  in transaction .I am also
> doing for update select on the
> "a" and "b" tables itself ,that is:
> select b from a  where pkey in (5,6,7) for update;
>
> My problems:
> [1] Is the for update lock anouth here?
> [2] The "for update" queries  HAVE to be done in the same order in all
> application which is pretty error prone -
> it is very easy to forget in one place and get a deadlock.
> [3] Can i make a trigger that automatically locks the counter_type='b'
> if a for update select was done on table b?
> something like (pseudo):
> trigger on select for update table b{
>    select for update where ident = OLD.pkey ;
> }
> [4] Can i combine queries for multiple tables to make locking atomic:
> select 1 from b,counter where b.pkey in (1,2,3,4) and counter.ident in
> (1,2,3,4);
> Hope for help and sorry for long message.
> evgeny
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org

Thanks for answer.However i have already searched for a way to make count
faster and didn't find anything.
Any pointers will be appreciated.
Thanks.
Evgeny.

On Wed, 28 Dec 2005, Bruno Wolff III wrote:

> On Tue, Dec 27, 2005 at 11:48:55 +0200,
> Tsirkin Evgeny <tsurkin@mail.jct.ac.il> wrote:
> >
> > Hi list!
> > My issue is as follows :
> > I have to do somecalculations based on *exact* number of rows in 2
> > tables (with a filter) meaning:
> > SELECT count(*) FROM a WHERE a.row1 in (1,2,3,4);
> > SELECT count(*) FROM b WHERE b.row1 in (1,2,3,4);
> > However i couldn't use the count(*) since it is too slow beacause of the
> > table size.
>
> You should look through the past archives on this subject. There is a way to
> do this that uses MVCC for incremental changes. You do want to make a sweep
> through the delta table periodically which will need stronger locking, but
> this won't block reads on a and b.
>
> > So,i created a trigger that on insert increments and on delete
> > decriments special "counter" table
> > that contains
> > \d counter
> >  Column    |         Type          | Modifiers
> > --------------+-----------------------+-----------
> > counter_type | character varying(30) |     ---- the table name
> > ident      | numeric(10,0)           |
> > count      | integer
> >
> >
> > The problem of course is thelocking issues while changing a and b
> > tables.What i am doing now is to
> > select 1 from counterwhere counter_type='a' and ident in (1,2,3,4)
> > for  update;
> > select 1 from counterwhere counter_type='b' and ident in (5,6,7) for
> > update;
> > Befor changing anything in tables "a" and "b"in transaction .I am also
> > doing for update select on the
> > "a" and "b" tables itself ,that is:
> > select b from awhere pkey in (5,6,7) for update;
> >
> > My problems:
> > [1] Is the for update lock anouthhere?
> > [2] The "for update" queriesHAVE to be done in the same order in all
> > application which is pretty error prone -
> > it is very easy to forget in one place and get a deadlock.
> > [3] Can i make a trigger that automatically locks the counter_type='b'
> > if a for update select was done on table b?
> > something like (pseudo):
> > trigger on select for update table b{
> >  select for update where ident = OLD.pkey ;
> > }
> > [4] Can i combine queries for multiple tables to make locking atomic:
> > select 1 from b,counter where b.pkey in (1,2,3,4) and counter.ident in
> > (1,2,3,4);
> > Hope for help and sorry for long message.
> > evgeny
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> >             http://archives.postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

On Mon, Jan 02, 2006 at 11:36:11AM +0200, Tsirkin Evgeny wrote:
> Thanks for answer.However i have already searched for a way to make count
> faster and didn't find anything.
> Any pointers will be appreciated.

What you want to do in the trigger is insert a new row into a table that
contains the change in count, instead of trying to update a single row
for each value/ident (btw, you'll probably get better performance if you
make ident an int instead of a numeric). So now you'll have a list of
changes to the value, which you will periodically want to roll up into a
table that just stores the count.

> On Wed, 28 Dec 2005, Bruno Wolff III wrote:
>
> > On Tue, Dec 27, 2005 at 11:48:55 +0200,
> > Tsirkin Evgeny <tsurkin@mail.jct.ac.il> wrote:
> > >
> > > Hi list!
> > > My issue is as follows :
> > > I have to do somecalculations based on *exact* number of rows in 2
> > > tables (with a filter) meaning:
> > > SELECT count(*) FROM a WHERE a.row1 in (1,2,3,4);
> > > SELECT count(*) FROM b WHERE b.row1 in (1,2,3,4);
> > > However i couldn't use the count(*) since it is too slow beacause of the
> > > table size.
> >
> > You should look through the past archives on this subject. There is a way to
> > do this that uses MVCC for incremental changes. You do want to make a sweep
> > through the delta table periodically which will need stronger locking, but
> > this won't block reads on a and b.
> >
> > > So,i created a trigger that on insert increments and on delete
> > > decriments special "counter" table
> > > that contains
> > > \d counter
> > >  Column    |         Type          | Modifiers
> > > --------------+-----------------------+-----------
> > > counter_type | character varying(30) |     ---- the table name
> > > ident      | numeric(10,0)           |
> > > count      | integer
> > >
> > >
> > > The problem of course is thelocking issues while changing a and b
> > > tables.What i am doing now is to
> > > select 1 from counterwhere counter_type='a' and ident in (1,2,3,4)
> > > for  update;
> > > select 1 from counterwhere counter_type='b' and ident in (5,6,7) for
> > > update;
> > > Befor changing anything in tables "a" and "b"in transaction .I am also
> > > doing for update select on the
> > > "a" and "b" tables itself ,that is:
> > > select b from awhere pkey in (5,6,7) for update;
> > >
> > > My problems:
> > > [1] Is the for update lock anouthhere?
> > > [2] The "for update" queriesHAVE to be done in the same order in all
> > > application which is pretty error prone -
> > > it is very easy to forget in one place and get a deadlock.
> > > [3] Can i make a trigger that automatically locks the counter_type='b'
> > > if a for update select was done on table b?
> > > something like (pseudo):
> > > trigger on select for update table b{
> > >  select for update where ident = OLD.pkey ;
> > > }
> > > [4] Can i combine queries for multiple tables to make locking atomic:
> > > select 1 from b,counter where b.pkey in (1,2,3,4) and counter.ident in
> > > (1,2,3,4);
> > > Hope for help and sorry for long message.
> > > evgeny
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Have you searched our list archives?
> > >
> > >             http://archives.postgresql.org
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Evgeny.

On Tue, 3 Jan 2006, Jim C. Nasby wrote:

> On Mon, Jan 02, 2006 at 11:36:11AM +0200, Tsirkin Evgeny wrote:
> > Thanks for answer.However i have already searched for a way to make count
> > faster and didn't find anything.
> > Any pointers will be appreciated.
>
> What you want to do in the trigger isinsert a new row into a table that
> contains the change in count, instead of trying to update a single row
> for each value/ident (btw, you'll probably get better performance if you
> make ident an int instead of a numeric).

Why?

> So now you'll have a list of
> changes to the value, which you will periodically want to roll up into a
> table that just stores the count.
Interesting idea.Thanks.However it pretty complicates things ,maybe there
will be simpler solution.Something i did not thought about at all - i think that
counting is something that everybody does.
[1] I have also a hope that i can create a trigger that locks counter
table once a 'select for update' was done on one of the tables i count.
However how can i say if a select that fires a trigger is a 'for update'
one?
[2] Maybe there is a MVCC or something solution like Bruno suggested (that
i did not realy understood thought).
Evgeny.
>
> > On Wed, 28 Dec 2005, Bruno Wolff III wrote:
> >
> > > On Tue, Dec 27, 2005 at 11:48:55 +0200,
> > > Tsirkin Evgeny <tsurkin@mail.jct.ac.il> wrote:
> > > >
> > > > Hi list!
> > > > My issue is as follows :
> > > > I have to do somecalculations based on *exact* number of rows in 2
> > > > tables (with a filter) meaning:
> > > > SELECT count(*) FROM a WHERE a.row1 in (1,2,3,4);
> > > > SELECT count(*) FROM b WHERE b.row1 in (1,2,3,4);
> > > > However i couldn't use the count(*) since it is too slow beacause of the
> > > > table size.
> > >
> > > You should look through the past archives on this subject. There is a way to
> > > do this that uses MVCC for incremental changes. You do want to make a sweep
> > > through the delta table periodically which will need stronger locking, but
> > > this won't block reads on a and b.
> > >
> > > > So,i created a trigger that on insert increments and on delete
> > > > decriments special "counter" table
> > > > that contains
> > > > \d counter
> > > >Column    |         Type          | Modifiers
> > > > --------------+-----------------------+-----------
> > > > counter_type | character varying(30) |   ---- the table name
> > > > ident    | numeric(10,0)           |
> > > > count    | integer
> > > >
> > > >
> > > > The problem of course is thelocking issues while changing a and b
> > > > tables.What i am doing now is to
> > > > select 1 from counterwhere counter_type='a' and ident in (1,2,3,4)
> > > > for update;
> > > > select 1 from counterwhere counter_type='b' and ident in (5,6,7) for
> > > > update;
> > > > Befor changing anything in tables "a" and "b"in transaction .I am also
> > > > doing for update select on the
> > > > "a" and "b" tables itself ,that is:
> > > > select b from awhere pkey in (5,6,7) for update;
> > > >
> > > > My problems:
> > > > [1] Is the for update lock anouthhere?
> > > > [2] The "for update" queriesHAVE to be done in the same order in all
> > > > application which is pretty error prone -
> > > > it is very easy to forget in one place and get a deadlock.
> > > > [3] Can i make a trigger that automatically locks the counter_type='b'
> > > > if a for update select was done on table b?
> > > > something like (pseudo):
> > > > trigger on select for update table b{
> > > >select for update where ident = OLD.pkey ;
> > > > }
> > > > [4] Can i combine queries for multiple tables to make locking atomic:
> > > > select 1 from b,counter where b.pkey in (1,2,3,4) and counter.ident in
> > > > (1,2,3,4);
> > > > Hope for help and sorry for long message.
> > > > evgeny
> > > >
> > > >
> > > >
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 4: Have you searched our list archives?
> > > >
> > > >         http://archives.postgresql.org
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 5: don't forget to increase your free space map settings
> > >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> >      subscribe-nomail command to majordomo@postgresql.org so that your
> >      message can get through to the mailing list cleanly
> >
>
> --
> Jim C. Nasby, Sr. Engineering Consultant    jnasby@pervasive.com
> Pervasive Software    http://pervasive.com    work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf     cell: 512-569-9461
>

On Wed, Jan 04, 2006 at 10:51:55 +0200,
  Tsirkin Evgeny <tsurkin@mail.jct.ac.il> wrote:
>
> > What you want to do in the trigger isinsert a new row into a table that
> > contains the change in count, instead of trying to update a single row
> > for each value/ident (btw, you'll probably get better performance if you
> > make ident an int instead of a numeric).
>
> Why?

Because this doesn't block other processes and still gives you correct
results.

>
> > So now you'll have a list of
> > changes to the value, which you will periodically want to roll up into a
> > table that just stores the count.
> Interesting idea.Thanks.However it pretty complicates things ,maybe there
> will be simpler solution.Something i did not thought about at all - i think that

Not that avoids blocking. If you don't have a lot of concurrent queries then
this may not be an issue for you.

> counting is something that everybody does.

No it isn't. Smetimes they aren't needed at all, sometimes approximate values
are good enough, and even when they are needed, it is often the case that
it is better for count queries to run slower so that other queries run faster.

> [1] I have also a hope that i can create a trigger that locks counter
> table once a 'select for update' was done on one of the tables i count.
> However how can i say if a select that fires a trigger is a 'for update'
> one?

If you have a counter table and do an UPDATE that will lock that row.
If you have multiple tables that you keep counts for you will want to lock
the whole counter table using a LOCK command or else you can get deadlocks.
Note this means that inserts and deletes from any of the tracked tables
will block inserts and deletes of those tables in other concurrent queries.

> [2] Maybe there is a MVCC or something solution like Bruno suggested (that
> i did not realy understood thought).

The explanation given at the top is the gist of the solution that uses MVCC
advantagesously.

Bruno Wolff III wrote:

>On Wed, Jan 04, 2006 at 10:51:55 +0200,
>  Tsirkin Evgeny <tsurkin@mail.jct.ac.il> wrote:
>
>
>>>What you want to do in the trigger isinsert a new row into a table that
>>>contains the change in count, instead of trying to update a single row
>>>for each value/ident (btw, you'll probably get better performance if you
>>>make ident an int instead of a numeric).
>>>
>>>
>>Why?
>>
>>
>
>Because this doesn't block other processes and still gives you correct
>results.
>
>
>
I understand THAT ,I meant why int will give me more performance.

>>>So now you'll have a list of
>>>changes to the value, which you will periodically want to roll up into a
>>>table that just stores the count.
>>>
>>>
>>Interesting idea.Thanks.However it pretty complicates things ,maybe there
>>will be simpler solution.Something i did not thought about at all - i think that
>>
>>
>
>Not that avoids blocking. If you don't have a lot of concurrent queries then
>this may not be an issue for you.
>
>
>
Well i DO have a lot of concurrent queries,that is the whole point.

>>counting is something that everybody does.
>>
>>
>
>No it isn't. Smetimes they aren't needed at all, sometimes approximate values
>are good enough, and even when they are needed, it is often the case that
>it is better for count queries to run slower so that other queries run faster.
>
>
>
It was just a hope of mine.

>>[1] I have also a hope that i can create a trigger that locks counter
>>table once a 'select for update' was done on one of the tables i count.
>>However how can i say if a select that fires a trigger is a 'for update'
>>one?
>>
>>
>
>If you have a counter table and do an UPDATE that will lock that row.
>If you have multiple tables that you keep counts for you will want to lock
>the whole counter table using a LOCK command or else you can get deadlocks.
>Note this means that inserts and deletes from any of the tracked tables
>will block inserts and deletes of those tables in other concurrent queries.
>
>
>
That does not help me .What i want is :
select 1 from table a where whatever for update;
issuing this should fire a trigger that does:
select 1 from table counter where a.pkey=counter.ident or something
however this should only be done for select FOR UPDATE!
And i can't know that inside the trigger!

>>[2] Maybe there is a MVCC or something solution like Bruno suggested (that
>>i did not realy understood thought).
>>
>>
>
>The explanation given at the top is the gist of the solution that uses MVCC
>advantagesously.
>
>
OK.

On Thu, Jan 05, 2006 at 10:34:31 +0200,
  Tsirkin Evgeny <tsurkin@mail.jct.ac.il> wrote:
>
> I understand THAT ,I meant why int will give me more performance.

Because if you have a bunch of processes sitting around waiting for table
locks, the average time to process queries will be higher. If you are
already limited by IO throughput or CPU usage this may not be a big deal,
but in many cases it will be.

> Well i DO have a lot of concurrent queries,that is the whole point.

Then you probably want to do what people have been recommending you do.

> That does not help me .What i want is :
> select 1 from table a where whatever for update;
> issuing this should fire a trigger that does:

You can't trigger on select statements. So this approach won't workin any case.

> select 1 from table counter where a.pkey=counter.ident or something
> however this should only be done for select FOR UPDATE!
> And i can't know that inside the trigger!

Bruno,thanks for the answer but this was not at ALL that i asked.
Neither mind .
Note however, how polite i am .
Thanks anyway.
Evgeny

Bruno Wolff III wrote:

>On Thu, Jan 05, 2006 at 10:34:31 +0200,
>  Tsirkin Evgeny <tsurkin@mail.jct.ac.il> wrote:
>
>
>>I understand THAT ,I meant why int will give me more performance.
>>
>>
>
>Because if you have a bunch of processes sitting around waiting for table
>locks, the average time to process queries will be higher. If you are
>already limited by IO throughput or CPU usage this may not be a big deal,
>but in many cases it will be.
>
>
>
>>Well i DO have a lot of concurrent queries,that is the whole point.
>>
>>
>
>Then you probably want to do what people have been recommending you do.
>
>
>
>>That does not help me .What i want is :
>>select 1 from table a where whatever for update;
>>issuing this should fire a trigger that does:
>>
>>
>
>You can't trigger on select statements. So this approach won't workin any case.
>
>
>
>>select 1 from table counter where a.pkey=counter.ident or something
>>however this should only be done for select FOR UPDATE!
>>And i can't know that inside the trigger!
>>
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>

I'm not really sure What Bruno answered that you didn't ask, but I'll
mention that anything you try here other than doing inserts and deletes
from your trigger is flat out going to perform poorly due to locking.
Plus, as you discovered, deadlocking will be a major issue, unless you
establish a table-level lock on your count table at the begining of the
transaction.

Personally, I'd just put the effort into setting up the insert/delete
stuff and a means to periodically roll that info up into a summary
table. I know that this has come up in the past, so you can probably
find someone else who's already done this and might be able to provide
you with code. Even if you have to write it from scratch, I doubt it's
more work than what you've already put into trying to get the other
scheme to work.

BTW, there is a desire to be able to store some kind of visibility info
in a manner which could be used by indexes; that might allow your select
count(*)'s to perform adequately without maintaining a seperate summary
table.

On Thu, Jan 05, 2006 at 10:53:40AM +0200, Tsirkin Evgeny wrote:
>
> Bruno,thanks for the answer but this was not at ALL that i asked.
> Neither mind .
> Note however, how polite i am .
> Thanks anyway.
> Evgeny
>
> Bruno Wolff III wrote:
>
> >On Thu, Jan 05, 2006 at 10:34:31 +0200,
> > Tsirkin Evgeny <tsurkin@mail.jct.ac.il> wrote:
> >
> >
> >>I understand THAT ,I meant why int will give me more performance.
> >>
> >>
> >
> >Because if you have a bunch of processes sitting around waiting for table
> >locks, the average time to process queries will be higher. If you are
> >already limited by IO throughput or CPU usage this may not be a big deal,
> >but in many cases it will be.
> >
> >
> >
> >>Well i DO have a lot of concurrent queries,that is the whole point.
> >>
> >>
> >
> >Then you probably want to do what people have been recommending you do.
> >
> >
> >
> >>That does not help me .What i want is :
> >>select 1 from table a where whatever for update;
> >>issuing this should fire a trigger that does:
> >>
> >>
> >
> >You can't trigger on select statements. So this approach won't workin any
> >case.
> >
> >
> >
> >>select 1 from table counter where a.pkey=counter.ident or something
> >>however this should only be done for select FOR UPDATE!
> >>And i can't know that inside the trigger!
> >>
> >>
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 1: if posting/reading through Usenet, please send an appropriate
> >      subscribe-nomail command to majordomo@postgresql.org so that your
> >      message can get through to the mailing list cleanly
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Jim C. Nasby wrote:

>Personally, I'd just put the effort into setting up the insert/delete
>stuff and a means to periodically roll that info up into a summary
>table. I know that this has come up in the past, so you can probably
>find someone else who's already done this and might be able to provide
>you with code. Even if you have to write it from scratch, I doubt it's
>more work than what you've already put into trying to get the other
>scheme to work.
>
Thanks.Do you think that using LISTEN/NOTIFY mechanism for rolling up
the summary table
could be used?

On Sun, Jan 08, 2006 at 10:57:21AM +0200, Tsirkin Evgeny wrote:
>
> Jim C. Nasby wrote:
>
> >Personally, I'd just put the effort into setting up the insert/delete
> >stuff and a means to periodically roll that info up into a summary
> >table. I know that this has come up in the past, so you can probably
> >find someone else who's already done this and might be able to provide
> >you with code. Even if you have to write it from scratch, I doubt it's
> >more work than what you've already put into trying to get the other
> >scheme to work.
> >
> Thanks.Do you think that using LISTEN/NOTIFY mechanism for rolling up
> the summary table
> could be used?

Sure, although on a busy system it might be better to just poll once a
minute or so. I'm not sure what the overhead for a NOTIFY is, but I
imagine it would start to add up if you were calling several times a
second.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461