I have a problem with locking/timeouts within one of my business objects.

I have a routine in my ControlBO that gets the last transaction reference number, increments the value by one, saves it and returns the value.

My posting routine makes a call for each record to my ControlBO.GetNextTransNumber to retrieve the next transaction number. (By the way, I am using transaction processing).

Everything works perfectly if I only have one record to process, however if I have more than one record to process, I get a timeout on the FillDataTable command on the second record trying to get the next transaction number.

Any ideas on why this is happening?

What isolation level are you using for your transaction processing?  Several of the isolation levels will lock the table on the first update, so you would be able to retrieve a record, but then after updating it, you wouldn't be able to query again until you committed or rolled back the transaction.

I am using IsolationLevel.ReadCommitted.  Did something change in 1.5 because I'm fairly certain my code worked under 1.4?

The issue also comes up when I try updating my inventory file.  If I have two or more of the same item in the same transaction, the first record works fine but subsequent reads fail.

Thanks,
Tim

I know for certain that the data access did not change from 1.4 to 1.5, and the transaction processing hasn't changed since 1.3... According to the documentation for ReadCommitted, SQL Server will use locks to prevent reading of uncommitted tables, you should probably use ReadUncommitted in this case.  From the .NET documentation:

	Member name	Description
	Chaos	The pending changes from more highly isolated transactions cannot be overwritten.
	ReadCommitted	Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.
	ReadUncommitted	A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored.
	RepeatableRead	Locks are placed on all data that is used in a query, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows are still possible.
	Serializable	A range lock is placed on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction is complete.
	Snapshot	Reduces blocking by storing a version of data that one application can read while another is modifying the same data. Indicates that from one transaction you cannot see changes made in other transactions, even if you requery.
	Unspecified	A different isolation level than the one specified is being used, but the level cannot be determined.  When using OdbcTransaction, if you do not set IsolationLevel or you set IsolationLevel to Unspecied, the transaction executes according to the default isolation level of the underlying ODBC driver.

Changing the Isolation level to ReadUncommitted did not solve the problem.  The program works fine without transactions so I guess I will have to experiment unless you have any other suggestions.

Nope, no other suggestions right now... but I'll keep thinking about it.

I ran into a similar situation,  look into using the

WITH (NOLOCK)

clause, that did the trick for me.

Tim,

Are you running your assembly from a network drive in this scenario?  There could be some additional trust and policy issues.

No, the problem is on my local development machine.

I just wanted to make sure.