How does one use FbTransactionOptions / TransactionBehavior to implement a wait?

Question

How does one use FbTransactionOptions / TransactionBehavior to implement a Wait?

I have found some info about using FbTransactionOptions / TransactionBehavior, but not enough details to implement.

string stCmd = "UPDATE " + stTableName + " SET " + liststFieldNamesNoKeyID[0] + " = @p0";
for (int iii = 1; iii < liststFieldNamesNoKeyID.Count(); iii++)
    stCmd += ", " + liststFieldNamesNoKeyID[iii] + " = @p" + iii.ToString();
stCmd += " WHERE" + stFieldKeyID + "= @p" + liststFieldNamesNoKeyID.Count().ToString();

FbTransaction fbTransaction = fbConn.BeginTransaction();
new FbTransactionOptions()
{
    TransactionBehavior = FbTransactionBehavior.Concurrency | 
                          FbTransactionBehavior.Wait
}; 

using (FbCommand fbCmd = new FbCommand(stCmd, fbConn, fbTransaction)) {
    for (int iii = 0; iii < liststFieldNamesNoKeyID.Count(); iii++) {
        string stPlaceHolder = "@p" + (iii).ToString();
        string stValue = liststNewValuesNoKeyID[iii];
        fbCmd.Parameters.AddWithValue(stPlaceHolder, stValue);
    }
    int iKeyID = Convert.ToInt32(stKeyID);
    fbCmd.Parameters.AddWithValue("@p" + liststFieldNamesNoKeyID.Count().ToString(), iKeyID);
    fbCmd.ExecuteNonQuery();
    fbTransaction.Commit();

In the above example, TransactionBehavior is not used. Rearranging causes the compiler to complain.

new FbTransactionOptions()
{
    TransactionBehavior = FbTransactionBehavior.Concurrency | 
                          FbTransactionBehavior.Wait
};
FbTransaction fbTransaction = fbConn.BeginTransaction(TransactionBehavior);

Also, how does one set the value of Wait?

score 2 · Answer 1 · answered Apr 15 '17 at 12:04

2

You need to use FbConnection.BeginTransaction(FbTransactionOptions options). And you need to pass it the instance of FbTransactionOptions you created, so:

var transactionOptions = new FbTransactionOptions()
{
    TransactionBehavior = FbTransactionBehavior.Concurrency | 
                          FbTransactionBehavior.Wait
}; 
FbTransaction fbTransaction = fbConn.BeginTransaction(transactionOptions);

Your original code didn't work because you only created an instance of FbTransactionOptions and immediately threw it away. The second attempt didn't work, because you don't have TransactionBehavior anywhere (it is a property of FbTransactionOptions, not a thing on its own).

answered Apr 15 '17 at 12:04

Mark Rotteveel

100,966
191
140
197

How do I set the value of Wait? – ttom Apr 15 '17 at 13:17
@ttom property `WaitTimeout` on `FbTransactionOptions`. Do you know that you can discover a lot of these things in Visual Studio? – Mark Rotteveel Apr 15 '17 at 13:28
I am unable to find relevant info about FbTransactionOptions FbTransactionBehavior.Wait / WaitTimeout on StackOverflow or MSDN forums. – ttom Apr 15 '17 at 17:57
@ttom I was referring to auto-completion suggesting properties. MSDN naturally won't provide documentation on a non-Microsoft product. I agree that there is not a lot of documentation for the Firebird ado.net provider, but that means that you have to dig a little deeper. – Mark Rotteveel Apr 15 '17 at 18:38
Two people accessing the same record simultaneously could happen. I suspect there is a standard way people handle this occasional occurrence. It's surprising to find no postings about this. – ttom Apr 15 '17 at 22:16
@ttom This is usually solved with a form of optimisitic concurrency: you don't lock the table, but will only notice on update, eg because you use something like `update ..., lastupdate = current_timestamp where lastupdated = `, if no record was updated, there was a conflict and the user needs to redo the work. For some suggestion about pessimistic locking, see [How to Lock a Record in InterBase/Firebird](https://www.ibphoenix.com/resources/documents/how_to/doc_109). This [tutorial](https://www.wisdomjobs.com/e-university/firebird-tutorial-210.html) also has explanations. – Mark Rotteveel Apr 16 '17 at 07:32

How does one use FbTransactionOptions / TransactionBehavior to implement a wait?

1 Answers1