Subject Re: [ib-support] Transactions in stored procedures
Author Jason Wharton
InterBase doesn't have a concept of Serialization. It is designed to allow
parallel operations to do all that they need to do with minimal conflicts
and problems. I know for some operations this is a critical aspect, but the
cost for true serialization is very penalizing in other areas. If you really
need truly serialized operations you will need to develop some sort of
locking mechanism to ensure your serialized stuff. As for other database
products, their ability to serialize more formally is just one of the side
benefits from having a poor architecture (non-MGA).

What I do when I want operations to be serialized is use a queue table and
then have a single process perform the operations. Since there is only a
single process taking action off of a queue, then you are guaranteed the
operations will be serialized. It can get a little tricky if you want to
perform the queue records in their POST order rather than in their COMMIT
order. It is possible for multiple transactions to generate entries in the
queue table and then take a little bit longer to commit than another

Jason Wharton
CPS - Mesa AZ

"Matteo Giacomazzi" <matteo.giacomazzi@...> wrote in message
> Hi Jason,
> Friday, July 05, 2002, you wrote:
> JW> Let me put it another way, executing a stored procedure is like
> JW> having it run in its own nested transaction. Either it succeeds
> JW> and all the work it performed is now a part of the clients
> JW> transaction or it fails (at any point) and all the work it
> JW> performed is cancelled and not a part of the client transaction.
> JW> So, the moral to the story is, whatever you need nested
> JW> transactions for just put all the work in a single stored proc and
> JW> you have your nested transaction.
> Okay, but what kind of isolation do I get in that way?
> I mean, may I trust on the fact that they are atomic?
> That is: if two different processes execute the same stored procedure
> are they "serialized" or not? Or they may "overlap" in some way?
> This is the real thing I'm interested in!
> Thank you!
> Kind regards,
> --
> Matteo
> mailto:matteo.giacomazzi@...
> ICQ# 24075529