Solution to prevent database locking in ColdFusion?

Question

The application I'm working on should handle a lot of ajax requests that need to update some data in the database.

[Macromedia] [SQLServer JDBC driver] [SQLServer] Transaction (process ID 66) stalled | clipboard resources from another process and was selected as a victim of deadlock. Restart the deal.

For reading, I already used the WITH (NOLOCK) hint and prevented many reading errors.

What can I do to better manage the recording?

CFLock update code in CF?

Or is there a way to ask SQL Server to lock a row instead of a table?

Has anyone tried to implement CQRS? It seems to solve the problem, but I don't understand how to handle it:

• ID generation (now it uses Auto Increment in DB)
• How to cope with an update request if the server could not immediately send an error message to the client.

thanks

Answer 1

Here are my thoughts on this.

From a ColdFusion server server

I really believe that using <cflock> tags around your ColdFusion code that updates the database can prevent the deadlock problem on the database server. Using a named lock will make every call single-threaded. However, you can use server-side timeouts on ColdFusion, waiting for <cflock> if transactions take some time. Such handling of the ColdFusion server can also slow down the application. You can perform stress testing before and after to see how this method affects your application.

From the server side of the database

First of all, let me say that I do not think that deadlocks on the database server can be completely prevented, just minimized and handled accordingly. I Found This Link For TechNet For You - Minimize Lock . First sentence from this page:

Although deadlocks cannot be completely eliminated, the following coding rules can minimize the likelihood of deadlocks.

Here are the key points of this link. They describe each topic in detail, so please read the source.

Minimizing deadlocks can increase transaction throughput and reduce system overhead because there are fewer transactions:

• Rollback that cancels all the work performed by the transaction.
• It is repeated by applications because they were discarded during blocking.

To minimize deadlocks:

• Access objects in the same order.
• Avoid user interaction in transactions.
• Keep transactions short and in one batch.
• Use lower isolation.
• Use row-based isolation level.
  • Set the READ_COMMITTED_SNAPSHOT database parameter to ON to enable fixed transaction transactions to use row versioning.
  • Use snapshot isolation.
• Use linked connections.

"Version-based row-based isolation level" may answer your question. Or is there a way to ask SQL Server to lock a row instead of a table ?. This source has some notes mentioned in the original source.

Here are some other links that appeared during my search:

Prevent deadlocks with the NOLOCK hint

How to avoid sql deadlock?

Tips to avoid deadlocks? - In this mention of how careful you are when using the NOLOCK tooltip.

Deadlock Difficulty

Using isolation levels based on version version