Hi

I need to have a DataAccessAdapter which reads data from the DB inside a loop but will not be blocked when another process starts writing data to the table. How is this possible?

To explain further.... I have 1 app which monitors a table in a loop. I then have another app which writes data to the table. 1 of the dialogs in this app creates records in the table but only commits them when the Ok button is clicked. If Cancel is clicked it does a rollback.

The trouble I have is when this dialog is open and a record is added to the table, my read app locks and eventually times out since it is blocked by the transaction in the other app.

Thanks for any help!

Hi buckbuchanan,

Could you post the relevant code of the writer application? I thnk this should be done using the peper isolation level. What isolation level are you using?

daelmo wrote:

Hi buckbuchanan,

Could you post the relevant code of the writer application? I thnk this should be done using the peper isolation level. What isolation level are you using?

Hi daelmo

Many thanks for your reply. What is peper?

The reader is using isolation level ReadCommitted. I didn't want to pick up data that hadn't been committed since the reader notifies other things that the data has changed.

The writer code goes something like this....

// Constructor _daAdapter = new DataAccessAdapter(connectionString, true); _daAdapter.StartTransaction(IsolationLevel.ReadCommitted, "UpdatePersonAttributes");

//"Add" clicked AttributeEntity ent = new AttributeEntity(); // ... set properties on entity if (daAdapter.SaveEntity(ent, true)) _attributeCol.Add(ent); // add entity to a collection

// "Ok" clicked _daAdapter.SaveEntityCollection(_attributeCol, true, true); _daAdapter.Commit();

The writer code goes something like this....

// Constructor _daAdapter = new DataAccessAdapter(connectionString, true); _daAdapter.StartTransaction(IsolationLevel.ReadCommitted, "UpdatePersonAttributes");

//"Add" clicked AttributeEntity ent = new AttributeEntity(); // ... set properties on entity if (daAdapter.SaveEntity(ent, true)) _attributeCol.Add(ent); // add entity to a collection

// "Ok" clicked _daAdapter.SaveEntityCollection(_attributeCol, true, true); _daAdapter.Commit();

In general it's not recommended to have a transaction waiting for a user interaction to be commited or canceled. Rather hold the inserted entities in memory and when the user click Ok insert them all using a transaction if you wish. You can also use a Unit Of Work which implecitly uses a transaction when commiting.

Otherwise use ReadUncommited as the isolation level.

Hi Walaa

Many thanks for the reply.

I take your point on not waiting for user input and we will definately fix that.

But I still have concerns that my reader has the risk of being locked out by another process or application. Is there no way to run a query through llblgen which will read data without paying any attention to the locks?

I don't want to use ReadUncommitted because the reader notifies other processes that data has changed and as some of the data can be rolled back I would be sending false notifications..

Any ideas??

So you need to specify NOLOCK.

Please check the following threads: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=10512 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=2482

Hi - thanks again for the reply.

Correct me if I am wrong... having read the posts it looks to me like this is the same as using READ UNCOMMITTED? If so then this will return un-committed records to me which I don't want.

Could you please clarify this?

Also... I have added this line of code where I set my connection string:

DynamicQueryEngine.UseNoLockHintOnSelects = true;

But nothing has changed!

Am I doing something wrong?

buckbuchanan wrote:

Hi - thanks again for the reply.

Correct me if I am wrong... having read the posts it looks to me like this is the same as using READ UNCOMMITTED? If so then this will return un-committed records to me which I don't want.

Could you please clarify this?

Also... I have added this line of code where I set my connection string:

DynamicQueryEngine.UseNoLockHintOnSelects = true;

But nothing has changed!

Am I doing something wrong?

If I understand you correctly, you want to have a reader read rows from a table without being blocked at all by any writer? That can only be accomplished if the reader has NOLOCK in its reads: every write will ALWAYS set a lock on the row written, you can't avoid that. The nolock hint you enabled should enable selects with NOLOCK added to the select statement. The queries you get when you enable DQE tracing (manual -> using the generated code -> troubleshooting and debugging), do you see NOLOCK being appended to the query?

Though I think you should rework the order in which things take place. A write to the DB shoul d be final: the decision has been made: data has to be written. So you should postpone that action till the decision has been made. Until then, don't write to the db. If a user has to confirm a write, write when the confirmation comes in, as that's the ONLY spot where you have the decision that the write shall take place.

A reader which polls a table is never blocked for a long period of time: the read is very quick, so writers won't be blocked and readers won't block the writers. So I think if you reschedule your write it will be OK.

The other thing you can do is add a flag field for validity to the row written: when the user clicks OK to confirm, the row is UPDATED so the validity flag is set to true. The reader should ignore rows with that flag set to false.

Hi Otis

Many thanks for taking the time to reply.

I have had a look at the profiler and I can see the nolock option being appended to the queries and the problem has now resolved.

I agree with your comments about the writer app and it is now being changed. However, I am concerned that if an app can bring the system to it's knees so could anything else (a long report being run, someone querying the DB, etc). This is why I want the read process to be un-aware of locks.

So, the only outstanding question I have is that after reading the threads posted above it looks to me like adding "nolocks" is the same as using READUNCOMMITTED. Is this really the case? I can't allow this process to pick up uncommitted data.

Thanks

Will

buckbuchanan wrote:

Hi Otis

Many thanks for taking the time to reply.

I have had a look at the profiler and I can see the nolock option being appended to the queries and the problem has now resolved.

I agree with your comments about the writer app and it is now being changed. However, I am concerned that if an app can bring the system to it's knees so could anything else (a long report being run, someone querying the DB, etc). This is why I want the read process to be un-aware of locks.

So, the only outstanding question I have is that after reading the threads posted above it looks to me like adding "nolocks" is the same as using READUNCOMMITTED. Is this really the case? I can't allow this process to pick up uncommitted data.

You can't have it both ways. The locks are set by the RDBMS to prevent readers reading the rows which are altered. The transaction isolation of the WRITER determines if readers can read the locked rows. As the reader has NOLOCK set, it reads all rows regardless of locks, so it will read uncommitted data.

You can't have a deadlock by two processes working on the same table, or at least it's not common. You can have a deadlock with 1 process working on the table with 2 threads, where one thread places locks, CPU goes over to other thread, thread place locks, CPU goes back to first thread, thread can't proceed as it has to wait for the locks of other thread to be lifted, but those will never be lifted as that thread has to wait for the first thread's locks to be lifted.

What you could do is creating a process queue (just a queue in which you place objects what to do, and a thread reads them one at a time and executes them) for the operations on this table.

What's also an option is to create a way to avoid having to poll the table. Polling is expensive, you could research the option where the receiver of the polling result will receive a message, call, object from the routine which will WRITE a value in the table: e.g.: routine X writes an entity to the table, calls afterwards routine Y which will then either poll or if that's unnecessary because X has passed on the info what's been changed to Y, send the changes to the receiver of the polling result.

Hi Otis

Many thanks again for your reply - that really clarifies how it works (and confirms my fears).

I was under the understanding (from a distant and probably distorted memory of working with Oracle) that the table wouldn't be locked until the data was being written (as the result of a commit), and until that time the data was held in a buffer - obviously this is not the case and that is why I am getting locked out. So... I am going to have to re-think how this should work.

Thanks for the suggestions for working around this problem.

buckbuchanan wrote:

Hi Otis

Many thanks again for your reply - that really clarifies how it works (and confirms my fears).

I was under the understanding (from a distant and probably distorted memory of working with Oracle) that the table wouldn't be locked until the data was being written (as the result of a commit), and until that time the data was held in a buffer - obviously this is not the case and that is why I am getting locked out. So... I am going to have to re-think how this should work.

Thanks for the suggestions for working around this problem.

Oracle is indeed ahead of sqlserver in this. Oracle uses MVCC (Multi version concurrency control), where readers don't block writers and vice versa. Sqlserver on the other hand doesn't contain such a feature.