Subject RE: [firebird-support] Re: Store procedure hangs clients ??
Author Alan McDonald
> I'm sorry to take your attention somewhere else but: DO all of us
> agree thaat my original problem with the SP is just commiting
> transaction ???
> Somebody please confirm this. For the nature of my application is not
> very easy to verify everything is fine untill some user complains and
> confirms everything is wrong, and that's not very nice for them.
> Pablo

The ODBC drive will make atomic transactions for you on each statemetn as
log as you disconnect after each statement. Good for website use since the
connection is ended after every page is delivered. Not so if you use it for
application which runs for some time on an open connection. You need to
start and end your transactions by bracketing the statement you wish to
aggrgate in said transaction. That's how I understand it anyway.

BTW - are you using this SP to create primary key values? If so, then you
need to look closely at your transaction isolation at the connection level.
You may be causing other users to wait until the person who hits this SP
finally commits or disconnects (same thing as abaove) from the db.

This is another argument for not using this method for PKs. Use a generator
and create another field for use by the client and his/her need for a
running sequence of field values.