Hello,

I searched through the forums and found some threads related to this problem. In general, it related to deadlocks killing read operations in favor of write operations. The addition of a transaction argument to the fill methods of typedlist and typedview was supposed to correct this in some instances.

My question is, has anyone run into _insert _and update actions being the victims of deadlocks? We are running into this now. We get:

============================================

Exception: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of an action query: Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

every so often, and the victim is always reported from a method which includes an insert or update -- never a read. It's also always on the same entity, so we have a good starting point, but I wanted to see if anyone else has seen this.

We do use typed lists on our site, but if another user was accessing a page with a typed list and there was a deadlock, wouldn't the read operation be the victim?

Thanks for any feedback,

Jason

On sqlserver, readers are blocked by writers. A write on a row causes an exclusive lock on that row. Any thread / process other than the process doing the writing (and thus holding the lock) will have to wait till the lock is removed.

It is key that you batch the statements in a transaction as much as possible, and only start a transaction for write operations, as read operations don't require a DB transaction.

Deadlocks occur when you have 2 transactions writing rows and reading eachothers written rows. SqlServer will then kill one process off as the deadlock victim. When this happens it is best to re-examine the flow in your application so you avoid deadlocks in the future: do as much READING up front prior to starting a transaction, do as much writing inside a single transaction, start a transaction as late as possible and commit a transaction as soon as possible.

Otis,

I Iam having same problem.

This error could be generated because of ON UPDATE CASCADE in the relations? Can I use another thing to avoid use ON UPDATE CASCADE?

Tks,

Djow

PS: Sorry about my english.....

Hi,

The On Update cascade is use to update a primary key (that has been changed) to the related table that has the foreing key to the table that changed.

To avoid the On Update cascade in my DB design: - Create a Identity column (Int16, Int32, etc) the type depend of the amount of records that you expect, set this column as PK. - Create a code column (normally string type, but could be any), set this column as unique. This is the column that your user use to locate a record, for an user this is the primary key. You as programmer can use this column or the PK column to locate a record. This allow you to change the code that the user use, without needing to change the primary key in the related tables.

please consult "SQL Server Books Online"

Search for Minimizing deadlocks, or Avoiding deadlocaks (or just Type "deadlocks" in the index tab and then scroll to the underlying "avoiding")

There is an interesting article there that gives you hints about how to avoid them.

The following are the main points cut from there: Although deadlocks cannot be avoided completely, the number of deadlocks can be minimized. Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:

1- Rolled back, undoing all the work performed by the transaction. 2- Resubmitted by applications because they were rolled back when deadlocked. To help minimize deadlocks: 3- Access objects in the same order. 4- Avoid user interaction in transactions. 5- Keep transactions short and in one batch. 6- Use a low isolation level. 7- Use bound connections.

Ok ... Thank you everybody....

Another question,

Where I can use "D.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.UseNoLockHintOnSelects = true" ?

I saw that in a thread in this forum and this is to use NOLOCK on SELECTS.

tks

From the reference documentation

Flag to signal the DQE to tell the SqkServerSpecificCreator to create WITH NOLOCK hints on table references in select queries for FROM clauses. WITH NOLOCK is not recommended though in high volume databases with a lot of rows, it can be a lot of blocking can occur because SqlServer forces readers to block writers and vice versa. Please consult the SqlServer's Book online for more information about this hint and also the downsides for using it. The setting is global and for all select queries. Default is false and it should only be set to true in those situations in which no other option is available.