Is there a way to lock one record before starting to modify that record?

Question

string stOpenConn = new FbConnectionStringBuilder {
    Database = stPathFilename,
    UserID = stUserID,
    Password = stPassword,
    ServerType = FbServerType.Embedded,
    ClientLibrary = stCLIENT_LIBRARY
}.ToString();

try {
    using(FbConnection fbConn = new FbConnection(stOpenConn)) {
        fbConn.Open();

        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();
        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();
        }//using
    }//using
}//try

And then releasing after modification is complete?

I presume that the record lock occurs after the connection is open ... before the BeginTransaction ... and releasing the record lock after the fbTransaction.Commit.

That is what transactions are for. What are you trying to achieve? — Mark Rotteveel, Apr 09 '17 at 19:40
The purpose is to keep two people from changing the same record simultaneously. Based on your comment, Mark Rotteveel, it appears that transactions lock the record. I had performed an Internet search prior to this post ... saw nothing that tied transactions to locking a record. Thanks for the clarification! — ttom, Apr 09 '17 at 23:36
If you update, then another transaction cannot update that same record, while the first transaction has not been committed. — Mark Rotteveel, Apr 10 '17 at 08:57
@WilliamXifaras In Firebird, trying to update a record that was already updated by another - still active - transaction will result in an update conflict, what depends on the transaction isolation is whether you are allowed to update the record after that transaction has committed. — Mark Rotteveel, Apr 10 '17 at 14:00
Is there a flag that lets the code know when a transaction is in process by another user? Perhaps one that is similar to a try / catch? — ttom, Apr 11 '17 at 01:09
Use a no-wait transaction (or a wait transaction with a timeout), and you get an exception if you try to modify a record that is already modified by another transaction. — Mark Rotteveel, Apr 11 '17 at 09:44
A search provides few "wait" / "no wait" details. It appears that a FbTransactionOptions and TransactionBehavior may work. ----- FbTransaction fbTransaction = fbConn.BeginTransaction(); new FbTransactionOptions() { TransactionBehavior = FbTransactionBehavior.Concurrency | FbTransactionBehavior.Wait }; ----- Is there a website that provides more details, along with an example? — ttom, Apr 12 '17 at 14:07

score 1 · Answer 1 · answered Apr 10 '17 at 18:09

Depending on how you want to do that, you may use (with care) the "WITH LOCK" clause of a select, which will turn into a pessimistic lock (that's why you need to be cautious when using it). More information at https://firebirdsql.org/refdocs/langrefupd21-notes-withlock.html A "dumb" update will also act as a pessimistic lock.

Is there a way to lock one record before starting to modify that record?

1 Answers1