.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SqlTransaction.BeginTransaction IsolationLevel Locking

Posted By:      Posted Date: October 02, 2010    Points: 0   Category :ASP.Net

Good Day,

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,
                                                 string mApplicantID,
                                                 string mYearTerm)
        SqlConnection conn = new SqlConnection(WebConfigurationManager.AppSettings[getDBConnString(mUnitCode)]);
        SqlCommand comm = new SqlCommand();
        SqlTransaction mDBLock;
        string mRetValue;

        comm = conn.CreateCommand();
        mDBLock = conn.BeginTransaction();

View Complete Post

More Related Resource Links

C++ Q&A: Locking Column Headers, Implementing Singleton Classes


Prevent the sizing of the column headers in an ATL composite control. Also, see how you can share a small amount of simple data among multiple processes running on the same machine without Remoting.

Paul DiLascia

MSDN Magazine June 2003

Infopath Template Deployment Locking Existing Records




We are currently using SharePoint Server 2007 and have multiple InfoPath lists configured and one workflow tasks list. We have workflows configured for these InfoPath lists using Visual Studio to design these workflows and use InfoPath forms as the main document. After using the workflow items for a while we have changed and upgraded the workflow for the last year. The issue came when we tried to upgrade to the third version we designed.




We have two InfoPath lists in SharePoint that have identical form templates. In both lists there are around 3000-3100 InfoPath forms give or take. I modified the template to accommodate some new fields and promoted additional fields but did not go over the

MergeContentDbs Error: : This SqlTransaction has completed; it is no longer usable

I am running a MOSS 2007 farm with a large (378 GB) content database. I am running through various splitting scenarios so that I have multiple Db's instead of just one large one.  Using mergecontentdb's works fine for site collections up to 2GB, but any site collection larger gets the following error: : This SqlTransaction has completed; it is no longer usable.   After that, the site is no longer browseable.  Fortunately this is my test bed, but it does not bode well for splitting up the larger site collections.   Any help is greatly appreciated.   DaleDale

Locking and refreshing a database

I have a web site that accesses and updates a MySQL database. Occasionally I need to reload certain tables in that database from a local SQL Server database. How can I programmatically: a) Establish that no-one is actually using the database b) Lock the MySQL database to prevent users accessing the web database while the re-load is in progress c) Test whether the database is locked - i.e. prevent visitors from attempting to access / update the database while the refresh is in progress Many thanks.

Locking a field after it has been selected

Hi all, Does anyone know if it is possible to lock a field after it has been selected... For example, we have a field called sign-off date....if possible, we would like for that field to be locked once its selected so it can't be changed in the future...is this possible out of box? Any help is greatly appreciated. Thanks!

Unit testing is locking up WCF service?

I am trying to write some unit tests (more integration tests actually) to hit a live IIS server hosting my WCF service.  Whenever I run a test though, if one of my Assert statements fails on the client side, my WCF service seems to lock up- and I have to do an iisreset to get things back online. For example, I have in a test method 3 calls from my service client to WCF service- Call1, Call2 and Call3.  The first time through, Call1 works great, Call2 works great and then Call3 fires the Assert because some data is not correct.  On the next time through the test, Call1 fails with the following error:   "An exception of type 'System.ServiceModel.CommunicationException' occurred in mscorlib.dll but was not handled in user code Additional information: An error occurred while receiving the HTTP response to http://localhost/Kiosk/KioskSite.svc. This could be due to the service endpoint binding not using the HTTP protocol. This could also be due to an HTTP request context being aborted by the server (possibly due to the service shutting down). See server logs for more details."   After doing an iisreset everything is okay again. Anyone have any ideas what might be going on?

Locking Page with progress message on postback

I have implemented a lockscreen based on the following articlehttp://www.4guysfromrolla.com/articles/121708-1.aspxIt does work for the most part but there are a couple of scenarios it causes issues and we ended up having to disable it.  Here is one example where it has issues.With a button on a page clicking the button calls the javascript to "lock" the screen with an overlay and show the progress message.In the button click I have the following code Response.ClearContent() Response.ContentType = "application/pdf" Response.AddHeader("content-disposition", "attachment;filename=TestFile.pdf") Response.WriteFile(Server.MapPath("TestFile.pdf")) Response.Write(Date.Now.ToLongDateString() & " " & Date.Now.ToLongTimeString()) Response.End() which will display the file download dialog box which is what I want.  What happens though is that my lock screen stays on in this scenario because with my button click code I'm overriding the response.  Is there an easy way around this?  The only way I have figured out is have my button click write out some javascript to open a new window that calls a .aspx page which has the button click code shown above in it to write out the file or have an invisible iframe on the page and load the .aspx with the button click code shown above.  Any other way ar

Cross server Selects by application are locking the Server

.Net Application runs cross server queries and show up on the SSMS Monitor as Locks.  The seem to be select statements, and when the application is restarted, the locks are released. What can be locking the system and how do we go about troubleshooting it? The application runs queries across sql2k8 and sql2k5. Compatiblity level on 2008 is set to 2008 (80).  Here are the details from the Activity Monitor. SessionID:51 login:Application  Wait time(ms): 183414  Wiat type :LCK_M_S Blocked By : 102  HostName: VHP-SQL01  Task State : Suspended Command: Select  application: MSSQL  Wait Time    Wait Resource  KEylock hotid =  dbid = 16 lockb2f64580 mode X associatedObjectID= 

The sqltransaction has completed; it is no longer usable


Hi All,

I am getting following error message while splitting sharepoint content databases using stsadm mergecontentdb command.

" The sqltransaction has completed; it is no longer usable "

For Information: 

  • I am using SharePoint Server 2007 Enterprise with Service Pack 2 updated (Version: 12.0.6425.1000) and SQL Server 2005 Enterprise with Service Pack 3 updated. Also I have enough space on my DB server for my content databases.

Any help in this regard will be greatly appreciated.



How does SqlConnection manages IsolationLevel?


This MSDN article states that:

An isolation level has connection-wide scope, and once set for a connection with the SET TRANSACTION ISOLATION LEVEL statement, it remains in effect until the connection is closed or another isolation level is set. When a connection is closed and returned to the pool, the isolation level from the last SET TRANSACTION ISOLATION LEVEL statement is retained. Subsequent connections reusing a pooled connection use the isolation level that was in effect at the time the connection is pooled.

The SqlConnection class has no member that may hold the isolation level. So how does a connection know what isolation level to run in???

The reason I'm asking this is because of the following scenario:

  1. I opened a transaction using TransactionScope in Serializable mode, say "T1".
  2. Opened a connection for T1.
  3. T1 is finished/disposed, connection goes back to connection pool.
  4. Called another query on same connection (after getting it from connection pool) and this query runs in serializable mode!!!


  1. How does the pooled

Transactions using SqlTransaction Class



Here is my code:

 Public Function transaction_via_dotnet(ByVal test As String) As Boolean

        Dim myTrans As SqlTransaction = objectconnection.BeginTransaction()

            Dim sql As String = "INSERT INTO Payment(DoctorID,Date,PaymentAmount,NoReceipt,BankName,AccountNo) Values('" & GetSetDoctor & "','" & GetSetdate & "','" & GetSetCost & "','" & GetSetReceipt & "','" & GetSetBankName & "'," & GetSetAccount & " )"

            Dim mycommand As New SqlCommand(sql, objectconnection, myTrans)

            mycommand.CommandText = "UPDATE Appointment SET DoctorPaymentID=(SELECT  MAX(PAYMENTID)as paymentID FROM PAYMENT) WHERE APPOINTMENTID=" & test

            Return True

        Catch ex As Exception

        End Try
    End Function

The problem is that the transaction is not working.For instance,now the code is correct so both sql statements are get

SQL Server 2005 Locking


We have a SQL Server 2005 stored procedure that updates the "batch_status" field of a specific row. This procedure is the one that is most frequently called in our entire system. It is typically called from within some third party software that moves information from queue to queue to queue. The last thing the software does is to update the status.

One of our custom applications shells out to this third party software, gets the process handle, and waits for the handle to close. Then, it finds out what the batch_status is to deterrmine what to do next.

As volume increases in our system it becomes more and more likely for us to get a dirty read and the affected data gets "stuck" rather than moved on to where it should go. I am extremely puzzled as to how the stored procedure is supposedly done, yet the change is not visible.

I don't know how to prevent these dirty reads. Should I use some locking hint in the update statement? If so, should I use it within a transaction block? I am very concerned about the effect either of these would be on overall performance. If I could get just a rowlock that would be great. I'm concerned that page locks and tablelocks will create a "gridlock".

Any ideas?

How to troubleshoot locking LOCK_M_IX



I have batches of timeouts in SQL and system_health contains around 20 entries all waiting on LCK_M_IX. Majority of entries does not have SQL text entry associated with it ("Unable to retrieve SQL text").

I can run profiler and wait for next occurance (couple of time a day) but this is heavily used server and hence I can not log a lot of information. So which counter with which thresholds I need to put to find out why those locks are acquired and held?

SQL Server 2008 Management Studio locking up while not running anything



Restart Server that DB is running on

Restart workstation that SQL Server 2008 Management Studio is running on

Start SQL Server 2008 and open file I'm editing

Edit the SQL file while while monitoring memory and processor usage on the workstation

Processor starts to spike, intellisense stops working, memory usage begins to climb to an absurd amount ~7G, management studio becomes almost nonresponsive (i.e. I can type and the text takes 30 seconds to 5 minutes to show up)

After about 30 mintues, memory usage drops to around 1.5G and management studio becomes semi-usable again.  Intellisense sometimes goes back to working, sometimes everything has to be restarted again.

I've been working on this exact setup for 7 months now and this problem is new.  No, I am not executing any statements - I am editing text only.

Help needed interpreting locking



I captured join between lock tables and waiting tasks and output is below. Is it possible to figure out from this output what is curlpit of this behavior?




Locking down application.master related pages to end-users.


I see that end users can access ASPX pages (such as  "_layouts/settings.aspx" etc) that are related to application.master.

My goal is that when end users try to navigate any of these pages (either directly or through OOB links), I want to return them to an error page (or home page). The end users may still need to modify "my settings" page which also has "_layouts" in it. So I have to consider this as an exception.

I did search a bit and not getting right solution yet.

Please suggest.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend