--- In firebird-support@yahoogroups.com, Lucas Franzen <luc@r...>
wrote:

>
> Steve,
>
>
> sgharp schrieb:
> > Hi All,
> >
> > I have the following stored procedure that is failing with a
> > deadlock. I think I know why it's failing but I don't know how

to

> > resolve it.
>
> What do you think is the problem?
>
> >
> > create procedure spCalcPrice
> > as
> > declare variable iID Integer;
> > declare variable dPrice Double Precision;
> > begin
> > for select ID
> > from mytable
> > order by ID
> > into :iID
> > do
> > Begin
> > execute procedure spCacl(:iID) returning_values :dPrice;
> >
> > update mytable
> > set Price = :dPrice
> > where (ID = :iID);
> > End
> >
> > suspend;
>
> Remove this suspend, what it is good for in your case?
>
> > I'm getting a message, "deadlock. update conflicts with

concurrent

> > update." and the price is not being updated. How can I

structure

> > this to resolve this error?
>
> Are you sure it is *just* the procedure, there's nothing else

(another

> update) involved?
>
> Can you execute this procedure on its own?
>
> Is ID your primary key?
>
> Does your procedure spCalc do anything else, or just summing up?

I thought the problem had to do with using the same cursor for
select as I was for update but, the more I read the less I think
this.

I put a suspend at the end of all my stored procedures. I thought
it was required to wrap things up (commit records,etc.).

The deadlock is occurring on the update statement, not the call to
spCalc. The spCalc procedure is returning the correct value without
errors. The spCalc calculates a weighted price based on beginning
inventory, purchases and ending inventory. spCalc doesn't change
any data; it just performs several select on various tables and
calculates a return value.

The ID field is the primary key.

Steve