8
Aug
Tags: , | 4 Comments »

FbTransaction changes to support tables locking

Firebird has a feature allowing you to specify tables you want to lock (read or write and exclusive/protected/shared) when starting transaction. (Note that Firebird still uses MGA/MVCC. This is just a feature to support some scenarios.) We had constants in ADO.NET Provider for Firebird for some time, but using them resulted in wrong parameters being sent to the server and followed by exception. :)

Today I implemented support for this locking (tracker item, mailing list thread). That means sending proper sequences. The FbTransactionOptions class created earlier for timeout support was extended with new property LockTables. You can use to specify table name and lock specification. The lock specification there is in fact only subset of all options you can specify for transaction (same enumeration). You can put there whatever you want other options will be simply ignored.

Small example:

conn.BeginTransaction(new FbTransactionOptions()
	{
		TransactionBehavior = FbTransactionBehavior.ReadCommitted,  // etc.
		LockTables = new Dictionary<string, FbTransactionBehavior>
		{
			{ "TABLE_1", FbTransactionBehavior.LockWrite | FbTransactionBehavior.Shared },
			{ "TABLE_2", FbTransactionBehavior.LockWrite | FbTransactionBehavior.Exclusive }
		}
	});

Here I’m specifying that for TABLE_1 shared (huh :) ) write lock will be placed and for TABLE_2 exclusive (that sounds better, isn’t it?) write lock will be placed. Similarly you can go with LockRead.

Available right now in weekly builds and SVN.

There's 4 Comments So Far

  • Pierre Arnaud
    August 9th, 2010 at 05:13

    Thank you Jiri. You are just awesome :-)

  • Andrey
    December 22nd, 2010 at 21:38

    Hello, mr. Jiri

    Can I ask one question?

    There are two background threads in the client app, both are writing data at the same time in the same table using the same FbConnection instance. How can I set transaction options in order to
    - avoid deadlocks while parallel writes executing
    - lock the whole table (because each thread uses [current number of records inserted today] in the table while creating new record)

    Current set of options is:
    ————-
    cmd.Transaction = Connection.BeginTransaction(new FbTransactionOptions()
    {
    TransactionBehavior = FbTransactionBehavior.Write, // etc.
    LockTables = new Dictionary
    {
    { “F_NOTE”, FbTransactionBehavior.LockWrite | FbTransactionBehavior.Exclusive | FbTransactionBehavior.Wait }
    }
    });
    ————-

    But it results the exception “lock conflict on no wait transaction At procedure ‘F_NOTE_INSERT’ line: 26, col: 5″. There is a query in the stored procedure at the 26-5 (before new row insertion):
    ————-
    select COALESCE(max( f_note.code), 0) from f_note
    where cast(current_timestamp as date) = cast( f_note.created_date as date)
    into :vCode;
    ————-

    Is there any error in my CreateTransaction() code?

  • cincura.net
    December 22nd, 2010 at 22:23

    First and foremost, do not use FbConnection across threads. It’s not thread safe. You would need to create careful locking to be safe.

    Anyway, from your description, this feature will not help you with your scenario. You have to rethink what you’re doing. I recommend you to join the list and discuss it there, as here the place is limited, or contact me directly.

  • Andrey
    December 23rd, 2010 at 16:32

    Thank you Jiri. I shall redesign my approach and, if needed, follow your advice

Share your thoughts, leave a comment!