OK Gordon, then maybe we've found your deadlock, but first: My ancient Data
Definition Guide says "SUSPEND should not be used in executable procedures,
since the statements that follow it will never execute. Use EXIT instead to
indicate to the reader explicitly that the statement terminates the
procedure." Follow that advice and remove your SUSPENDs.

Your deadlock probably arises when your SP is executed twice for the same
PIN simultaneously. First, one call is made, and before the transaction the
SP operates within commits, another call is made. This second call will
find that PIN_IS_IN_USE <> 'Y' since only committed changes from other
transactions can be seen. However, when the SP gets to the point where is
shall set PIN_IS_IN_USE, this will fail since the other instance have done
exactly the same already - and this is probably where you get your deadlock.

A better way to do what you seem to be trying to do, could be to do a fake
update (i.e. something like UPDATE PINS SET PIN = PIN WHERE PIN = :PIN) or
use pessimistic locking rather than have the field PIN_IS_IN_USE. But I'll
leave any such advice for the more qualified Helen or Jason.

HTH,
Set