Hi,

> I have an application that process thousands of material transactions per

day.

> Today, i use some triggers in the transactions table to keep summary
> tables updated, but these triggers are slowing down my application.
> These summary tables are not used often, so there is no need to
> process them immediately, so i'm planning to use another approach:
>
> To create a stored procedure to access these summary tables, and when
> the procedure is called, it will process all pending transactions that
> was not processed yet, updating the summary tables and returning the
> result i need.
>
> My question is how can i guarantee that when two or more clients call
> the procedure at the same time, just the first one can update the
> summary tables, while the other clients waits the end of the process?

Well, the easy way would be to let your procedure somehow know
that it's running. For example, by incrementing a generator and decrementing
it when it's done.

When the second application calls the procedure, check the generator value
and if it's 1 (or whatever, but 0), then you exit the procedure.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com