context:
newbie
firebird (pretty sure) classic installed from
Firebird-1.5.3.4870-0-Win32.exe
ibpp components (obtained in last 6 months or so)
bds2006 c++ (command line bcc32)
fast pentium 4 hyper-threaded CPU, 2GB ram, plenty of disk

issue:
I am receiving a deadlock return in a situation that I believe (at
least I want :) ) to "wait".

Further below are isql scripts to do some setup, and source for a test
driver program using IBPP components that should demonstrate the
issue. (I originally encountered the issue using SQLAPI components.)

I am basically trying to update a value (similar to a generator), and
return that "next" value to serve multiple requesting clients (only
one value to one client at a time). I believe I have structured and
arranged everything so that this should be the case, but when I run
two instances of the program, I pretty quickly encounter a deadlock
(with one program terminating, and the other continuing to run, as it
is currently written.)

(Seems that amWrite, ilReadCommitted, rlWait (and supposedly default
of isc_tpb_no_rec_version - doesn't appear to be specifiable to IBPP),
would be appropriate to accomplish this. Also tried ilConcurrency in
place of ilReadCommitted, both yield deadlock.)

After initial setup and connection, the program starts a transaction
and calls a stored procedure that performs an update to a record, and
then selects the updated value from the record to return. The
calling program additionally takes the returned value and performs an
insert into a different table, still within the same transaction.
Then, the transaction is committed.

Multiple clients following this sequence, _should_ (I think) therefore
all be "held" at the attempt to update the ("generator") record, until
the other transaction (if any) is released. (I've tried to select
transaction parameters that I thought would accomplish this, as above.)

But, instead of working as expected, I get a deadlock returned.

Why? (I don't see any contended for resources that shouldn't be
"wait"ed for...)
Is this firebird behaviour working as designed, and intended?
Is there any way to avoid this (deadlock) behaviour for this scenario?
In what cases are outstanding locks waited for?

Thanks.

[side issue: I've also just noticed that fbserver is currently
consuming all of one cpu, but nothing should be talking to it. Maybe
its just sweep'in up after my earlier runs, although I would have
expected it to complete those long ago. I did abort the program
several times...]

supporting items:
-----------------------------------------------------
/* isql script to setup databases, tables, and procedures
adjust paths appropriately for your system.
*/
CREATE DATABASE "c:\temp\junkdb2.gdb" USER "SYSDBA" PASSWORD
"masterkey" PAGE_SIZE=1024;

create table tbl_sysuids (col1 bigint, col2 bigint) ;

commit ;

insert into tbl_sysuids values(7, 0) ;
insert into tbl_sysuids values(8, 0) ;

commit ;

create table tbl_trackkeys (col1 varchar(20)) ;
commit ;

set term ^ ;

create procedure get_somesysuid (colkey integer)
returns (sysuid bigint)
as
begin
update tbl_sysuids set col2 = col2 + 1 where col1 = :colkey ;
select col2 from tbl_sysuids where col1 = :colkey into sysuid ;
suspend ;
end ;
^
commit ^

set term ; ^

----------------------------------------------------------------
/*You may wish to change forloop value to run long enough to make it
easy to run multiple instances of program concurrently.*/
/* cpp demo source - compiled with bds2006 bcc32, pretty generic
expect it will work with gcc or vc
*/
//#define IBPP_WINDOWS

//#ifdef IBPP_WINDOWS
#include <windows.h>
//#endif

#include <ibpp/core/ibpp.h>

//using namespace IBPP ;

#include <iostream>
#include <stdio.h>
#include <typeinfo>

#if 1
const char* DbName = "C:/temp/junkdb.gdb"; // FDB extension (GDB is
hacked by Windows Me/XP "System Restore")
// const char* BkName = "C:/test.fbk";
const std::string ServerName = "localhost"; // Change to "" for local
protocol / embedded
// const std::string ServerName = "192.168.1.53"; // Change to "" for
local protocol / embedded
#endif

// The tests use by default the well-known default of SYSDBA/masterkey
// credentials. Do not forget to update them if required.

const std::string UserName = "SYSDBA";
const std::string Password = "masterkey";


void Test3()
{
printf(_("Test 3 --- Exercise basic DDL operations and
IBPP::Exceptions\n"));

IBPP::Database db1;
db1 = IBPP::DatabaseFactory(ServerName, DbName, UserName, Password);
printf("About to connect...\n") ;
db1->Connect();
printf("connected.\n") ;
// The following transaction configuration values are the defaults and
// those parameters could have as well be omitted to simplify writing.
IBPP::Transaction tr1 = IBPP::TransactionFactory(db1,
// IBPP::amWrite, IBPP::ilConcurrency, IBPP::lrWait);
IBPP::amWrite, IBPP::ilReadCommitted, IBPP::lrWait);
// tr1->Start();
printf("Declaring statements...\n") ;
IBPP::Statement st1 = IBPP::StatementFactory(db1, tr1);
IBPP::Statement st2 = IBPP::StatementFactory(db1, tr1) ;
printf("Before try:\n") ;
try
{
unsigned ui ;
for(ui=0 ; ui < 5 ; ui++)
// for(ui=0 ; ui < 100000 ; ui++)
{

tr1->Start(); //Does this have to be started before being assoc'd
with state (above)?

//printf("Before st1->Prepare()\n") ;
st1->Prepare("select * from get_somesysuid(7)") ;
// st1->Prepare("execute procedure get_somesysuid(7)") ;
//printf("Before st1->Execute()\n") ;
st1->Execute() ;
//printf("Before st1->Fetch()\n") ;
if(st1->Fetch())
{
char idbuf[256] ;
std::string sidbuf ;
__int64 ival ;
if(st1->IsNull(1))
printf("col 1 is NULL!\n") ;
// printf("col 1 name is %s\n", st1->ColumnName(1)) ;
//printf("Before st1->Get()\n") ;
// st1->Get(1, idbuf) ;
// st1->Get(1, sidbuf) ;
// st1->Get(1, sidbuf) ;
st1->Get(1, ival) ;
//ibpp tests use a cstring[] character array, but it fails for me on a
varchar...
//(maybe there type wasn't a varchar?)
//printf("Obtained %s\n", idbuf) ;
//printf("Obtained %ld\n", ival) ;
st2->Prepare("insert into tbl_trackkeys values(?)") ;
//printf("Before st2->Set()\n") ;
sprintf(idbuf, "%ld", ival) ;
st2->Set(1, idbuf) ;
//printf("Before st2->Execute()\n") ;
st2->Execute() ;
}
else
printf("st1->Fetch() false\n") ;


// tr1->CommitRetain();
tr1->Commit();
}//for
printf("(ui %u)\n", ui) ;

}

catch(IBPP::SQLException& e)
{
std::cout<< e.what();
//~ std::cout<< e.what();

if (e.EngineCode() != 335544569)
{
// _Success = false;
printf(_("The error code returned by the engine during a\n"
"voluntary statement syntax error is unexpected.\n"));
}
}

// Intentionally do not Commit or Rollback the transaction, nor even
Disconnect
// The auto-release mechanisms will have to Rollback and terminate
everything cleanly.
}

int main(int argc, char *argv[])
{
Test3() ;
return 0 ;
}
-------------------------------------------------------------
rem commandline I used to compile test driver program
rem adjust command for your environment/tools
bcc32 -DIBPP_WINDOWS -Ic:\estsvn\combine\est ibppsysuid.cpp
c:\estsvn\combine\est\ibpp\core\all_in_one.cpp