please help me, i have a web application who uses a stored proc in our SQL Database and process the data with SqlTransaction.BeginTransaction. but we don't place any isolation level.
The stored proc have multiple processing like insert, update and large queries from multiple tables. we decided to place the SqlTransaction class in our code to access the commit and rollback method incase the stored proc has an error.
later when the user is using our website, they experience locking, by the way the average number of users are around 600 a day.
is it possible to eliminate the chance of locking when using the SqlTransaction class when we use an isolation level like "Snapshot".
if not, what level should we use.
please see sample code below
public string Generate_Student_Number_Update(string mUnitCode,
SqlConnection conn = new SqlConnection(WebConfigurationManager.AppSettings[getDBConnString(mUnitCode)]);
SqlCommand comm = new SqlCommand();
comm = conn.CreateCommand();
mDBLock = conn.BeginTransaction();
View Complete Post