Concurrency problem

[Jonny Bergdahl]

I have a GUI application that displays historical data, and a batch
application that loads new data into the database.

My problem is that while the baych application is updating the database, the
GUI application gets locked out. The batch application is using one
transaction for the update, which can run for up to 15 minutes.

Question is what properties do I need to set on the Connection/Command
objects to let the GUI ignore the locks and access the data anyway? Since it
it historic data it is not important that it is not fully up to date, what
is important is that the user is not locked out during the update.

I am using SQL Server 2005, ADO.NET with a MSDTC transaction, and C#.

Any help is much appreciated!

regards;
/jb

 

[Cor Ligthert[MVP]]

Johny,

Because of your message I assume that you have generated in a way the
construction of the SQL transact code.

Somewhere in your code is the SQL transact for the update.

In that there is a read for all the items or a read of the latest timespan.
There is checked in that if the previous read is equal to the existing data.

By removing that there is no concurrency checking anymore.
The optimistic concurrency checking is not done by SQL server, however by
the dynamic transact code that you use or the transact code in Stored
Procedures.

Cor

 

[William Vaughn \(MVP\)]

Question: Is it okay if the client application view the data while only part
of the batch has completed making changes? If so, I would break up the batch
into smaller transactions. I expect this would improve performance and limit
the scope of the locks to fewer pages. I would also look at the "batch
application" to see if it's efficient. Is it importing data using ADO
UPDATE/INSERT commands or is it using BCP/SSIS/SqlBulkCopy to import the
data and a server-side procedure to integrate it into the database?

I would also consider using Sync Services to keep the clients up to date.
This is drop-dead easy to do with the Local Data Cache (that I'll be
lecturing on during my session at VSLive next week in San Francisco).

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

 

[Colbert Zhou [MSFT]]

Hi Jonny,

Welcome to posting in ADO.NET newsgroup! This is Colbert Zhou [MSFT] and I
will be working on this issue with you.

In this post, I'd like to analyze the cause of this issue firstly, and then
I will provide three solutions. Each solution will also contain its
strength and weakness analysis.

----------------------------------
CAUSE:
The default transaction isolation level in SQL Server 2005 is "READ
COMMITTED WITH LOCKS", which means if one transaction is updating the data
source, before this transaction commits, other operation related to this
data source will be locked and waiting for the updating transaction ends.
So, that is why the GUI application will be locked if the updating batch
application starts.
For detail on transaction isolation levels, please see
http://msdn.microsoft.com/en-us/library/ms173763(SQL.90).aspx.


---------------------------------
SOLUTIONS:
Solution 1: Set Transaction Isolation Level to READ UNCOMMITTED
"READ UNCOMMITTED" level can let the reading transaction get data which is
modified by other transactions but not yet committed. Through this
method, the GUI application can read the data which has been modified by
the updating transaction anytime during the updating transaction is
working.
For how to set transaction isolation level to "READ UNCOMMITTED", you can
refer to http://msdn.microsoft.com/en-us/library/ms173763(SQL.90).aspx.

For C# codes to set transaction isolated level to "READ UNCOMMITTED",
please see following code snippet:

using (SqlConnection cn = new SqlConnection())
{
cn.ConnectionString = connectionString;
cn.Open();
using (SqlTransaction tran =
cn.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
{
// Query data
}
}

[Strength]
This method is easy to achieve.
[Weakness]
Each read operation should use a transaction, so it will affect the
application performance.
After one read query for the data source, the GUI application may get some
newly updated data and some original data. It may cause some confusion to
the user.



Solution 2: Set Transaction Isolation Level to SNAPSHOT
"SNAPSHOT" transaction isolation level can let the reading transaction get
the data which existed at the start of the updating transaction. So the
GUI application will retrieve the original data all the time when the
updating transaction is working. Meanwhile, to use this transaction
isolation level, we need to first turn on the database snapshot setting.
We can first turn on the database snapshot setting via this SQL command:

ALTER DATABASE DatabaseName
SET ALLOW_SNAPSHOT_ISOLATION ON

Then we can use the similar method to set transaction isolation level to
SNAPSHOT which I discussed in the first solution.

[Strength]
This method is also easy to achieve. Comparing to the first solution, when
the updating transaction is working, we will get the same data via this
method.
[Weakness]
Each reading query will be contained in a transaction, so this method will
also affect application performance.



Solution 3: Customize a Database Snapshot
Before we start updating transaction, we can also using SQL Server 2005 new
feature to create a database snapshot. So when the updating transaction is
working, we can operate the data in the database snapshot directly.
For detail introduction on database snapshot, please see
http://msdn.microsoft.com/en-us/library/ms175158(SQL.90).aspx.
For how to create a database snapshot in SQL Server 2005, please refer to
http://technet.microsoft.com/en-us/library/ms175876(SQL.90).aspx.

[Strength]
Through this method, there is no need to create a transaction to read the
data source, so if we need to query the data many times when the updating
transaction is working, this solution can improve application performance.
[Weakness]
This solution is more difficult to achieve than solution one and solution
two.


If you have any questions, please be free to let me know. Have a nice day,
Jonny!



Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jonny Bergdahl]

Clarification: The MSDTC transaction is only used in the batch application
(it talks to another database as well, thus the need for MSDTC). The GUI
application don't use any transactions, as it only reads data from the
database.

TransactionOptions tranOpt = new TransactionOptions() {
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
};
using (TransactionScope tran =
new TransactionScope(TransactionScopeOption.Required,tranOpt)) {
//here You go with ambient transaction
}

This means that to solve the problem I actually need to add a transaction to
my code. Seems rather counter productive.

Thanks, will try this out!

Regards;
/jb

 

[Jonny Bergdahl]

Solution 1: Set Transaction Isolation Level to READ UNCOMMITTED

"READ UNCOMMITTED" level can let the reading transaction get data which is

Seems this is the way I need to go.

Solution 3: Customize a Database Snapshot
Before we start updating transaction, we can also using SQL Server 2005
new
feature to create a database snapshot. So when the updating transaction
is
[Weakness]
This solution is more difficult to achieve than solution one and solution
two.

Another weakness seems to be that it is only supported by the Enterprise
version?

Regars;
/jb

 

[Colbert Zhou [MSFT]]

Yes, you are right. That is also a weakness. And I am sorry for fogetting
to mention that. We can find the limitation in this MSDN document,
http://msdn.microsoft.com/en-us/library/ms143761(SQL.90).aspx


Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

 

[Jesús]

Threre is another option: enabling READ COMMITTED SNAPSHOT isolation level
on the database:

ALTER DATABASE TheDatabaseName SET READ_COMMITTED_SNAPSHOT ON.

This option is the easiest. You don't need to do anything more. Now the
default isolation level is READ COMMITTED SNAPSHOT, so readers don't block
writers and writers don't block readers. It comes with a price however: row
versioning has to be maintained in tempdb.


On the other hand, the READ UNCOMMITTED isolation level option can be
implemented without an explicit transaction, you just need to execute the
command SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED just after you open
a connection. If you use the factory pattern for creating connections, it is
very easy to put the code to exectue the command in just one place on your
application.

Let me give an example:

public static class ConnectionFactory
{
private static string _connectionString;
private static string ConnectionString
{
get
{
if (string.IsNullOrEmpty(_connectionString))
{
_connectionString =
ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
}
return _connectionString;
}
}

private static StateChangeEventHandler connectionStateChangeEventHandler
= new StateChangeEventHandler(OnConnectionStateChange);

public static SqlConnection CreateConnection()
{
SqlConnection cn = new SqlConnection(ConnectionString);
cn.StateChange += connectionStateChangeEventHandler;
return cn;
}

private static SqlCommand _setReadUncommittedIsolationLevelCommand;
private static SqlCommand SetReadUncommittedIsolationLevelCommand
{
get
{
if (_setReadUncommittedIsolationLevelCommand == null)
{
_setReadUncommittedIsolationLevelCommand = new SqlCommand();
_setReadUncommittedIsolationLevelCommand.CommandText = @"
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
";
}
return _setReadUncommittedIsolationLevelCommand;
}
}


private static void OnConnectionStateChange(object sender,
System.Data.StateChangeEventArgs e)
{
if (e.CurrentState == System.Data.ConnectionState.Open)
{
SetReadUncommittedIsolationLevelCommand.Connection =
(SqlConnection)sender;
SetReadUncommittedIsolationLevelCommand.ExecuteNonQuery();
}
}

}


One you have defined ConnectionFactory class, you should always create
connections by calling ConnectionFactory.CreateConnection(). I you do that,
you are always in READ UNCOMMITTED isolation level.

Regards:

Jesús López

Solution 1: Set Transaction Isolation Level to READ UNCOMMITTED
"READ UNCOMMITTED" level can let the reading transaction get data which
is

Seems this is the way I need to go.

Solution 3: Customize a Database Snapshot
Before we start updating transaction, we can also using SQL Server 2005
new
feature to create a database snapshot. So when the updating transaction
is
[Weakness]
This solution is more difficult to achieve than solution one and solution
two.

Another weakness seems to be that it is only supported by the Enterprise
version?

Regars;
/jb

 

[Cor Ligthert[MVP]]

Johnny,

I did not make much attention that you was using a transaction.

A transaction is in my idea something that should be avoided as much as
possible, because it can lock your tables unwished completely

In this case therefore in my idea completely stupid.

Cor