Problem solve Get help with specific problems with your technologies, process and projects.

What is the best way to control locking behavior?

How can we avoid seeing so many lock timeouts in the database? What is the best way to control locking behavior?

Platform: Win2000, SQL Server 2000, ADO.NET in VB components
I would say you are having a locking problem, as many 'lock timeouts' are not a usual thing in ADO.NET. You should always use code similar to the following when dealing with DB connections:

using (SqlConnection cn = new SqlConnection("my connection string"))
	//your code here

This block ensures that the connection is always disposed (therefore closed). The compiler translates this to a try ... finally block that always calls cn.Dispose().

DataReaders are also disposable, so you can use them in a using block too. Recall that if your application logic returns an open reader to some other application layer, the connection remains opened (and potentially causes DB locks) until it's closed. As a general rule, don't depend on code you don't control to close your reader (and connections). Do what you need to do as fast as possible and close them. To pass data between layers, I prefer to pass DataSets or some other object representation that are disconnected.

Dig Deeper on .NET Architecture Best Practices

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.