Locking Problem

StrataFrame Forum

Locking Problem

http://forum.strataframe.net/Topic3779.aspx

By Tim Dol - 10/22/2006

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?

By StrataFrame Team - 10/23/2006

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.

By Tim Dol - 10/23/2006

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

By StrataFrame Team - 10/23/2006

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.

By Tim Dol - 10/23/2006

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.

By StrataFrame Team - 10/23/2006

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

By Scott - 10/23/2006

I ran into a similar situation, look into using the

WITH (NOLOCK)

clause, that did the trick for me.

By Trent L. Taylor - 10/23/2006

Tim,

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

By Tim Dol - 10/23/2006

No, the problem is on my local development machine.

By Trent L. Taylor - 10/23/2006

I just wanted to make sure. Smile

By Tim Dol - 10/23/2006

Thanks Scott, this solution worked fine, however I'm concerned if this is a work around or standard practice when implementing transactions. Based on the information Ben supplied with respect to isolation levels, the ReadUncommitted should have worked...Actually I tried them all and none of them worked for repeated reads.

Right now I have a FillByItemNumber method in my InventoryBO. I use this anytime I want to retrieve an inventory record. Does this mean I need to create another method FillByItemNumberNoLock and use this anytime I want to update the item muliple time within a transaction?

By Scott - 10/23/2006

That is exactly what happened to me. The only way I could get my import routine to work was with the NOLOCK. I don't know if this is the best way to do it but I couldn't find any other way to get it to work. If you come up with some thing please let me know, I will do the same.

Scott