[Firebird-net-provider] Transaction problem with .NET provider v. 1.7

[James W. <jlw...@ya...>]

Summary: I'm having a problem setting transaction attributes using the .NET provider v. 1.7. I'm using Firebird version 1.5.2. The BeginTransaction() method doesn't seem to be consistent with behavior inside ISQL. I'm looking for a workaround.
 
I have a table which is used by alot of concurrent transactions. This table contains a column used for doing counting. Each transaction updates this table at some point as follows:
 
update stats set count = count + 1 where month = <current month>;
 
Using ISQL I performed the following commands in two separate, concurrent sessions:
 
SQL> set transaction wait isolation level read committed no record_version;
SQL> update stats set count = count + 1 where month = 12;
SQL> commit;

When I performed these commands in two separate, concurrent sessions the second update dutifully waits until the first transaction has committed. It then commits with no problems. However, now I want to perform the same sequence of events from the firebird .NET provider. I set up the transaction as follows:
 
FbTransactionOptions opt = FbTransactionOptions.Wait |
 FbTransactionOptions.ReadCommitted |
 FbTransactionOptions.NoRecVersion;
txn = connection.BeginTransaction(opt);
...
String incrementCmd = "update stats set count = count + 1 " +
 "where month = '"+ month + ";";
command = new FbCommand(incrementCmd, connection,txn);
command.ExecuteNonQuery();
...
txn.Commit();
 
This is part of a larger transaction which includes only a bunch of inserts into other tables. When this code gets going with 5 or 10 concurrent transactions going, I start getting errors of the form:
 
Exception: FirebirdSql.Data.Firebird.FbException
Message: deadlock
update conflicts with concurrent update
Source: FirebirdSql.Data.Firebird
   at FirebirdSql.Data.Firebird.FbCommand.ExecuteNonQuery()
   at EagleEye.EmailParser.FileHandler.enterEmailIntoDatabase() in c:\documents and settings\james walker\my documents\visual studio projects\eagleeye\emailparser\filehandler.cs:line 676

The line number indicated is the ExecuteNonQuery that does the stats update. Can you see anything that I'm doing wrong? Is this a known problem? Is there a workaround?
 
-- Les Walker
 

		
---------------------------------
Do you Yahoo!?
 Take Yahoo! Mail with you! Get it on your mobile phone.