C# .NET - How to lock the records while updating user other user cannot update

Asked By Tahir khan
19-Jan-12 01:14 AM
How to lock the records if one user is login and updating and other user cant able to edit and getting a error message 
  D Company replied to Tahir khan
19-Jan-12 01:17 AM
Hello Tahir,

In yesterday post we mentioned about threadin for console application, please do reffer that post, surely you will get idea about this.

or

let me know if you want some coding sample as well


Regards
D
  Vickey F replied to Tahir khan
19-Jan-12 01:19 AM
When multiple users attempt to simultaneously update a record, the database locking model ensures that the update from each user succeeds as a unit, but each update overwrites the previous update.

An explicit (pessimistic) locking model prevents unexpected loss of data updates and controls the workflow when simultaneous updates from more than one user occur.

There are two locking models that can be used:
  • Optimistic locking - A locking technique that allows simultaneous updates and detects conflicts in updates when the record is committed. By default ClearQuest uses optimistic locking.
  • Pessimistic locking - A locking technique that guarantees exclusive access to the record while it is being edited.
  Riley K replied to Tahir khan
19-Jan-12 01:37 AM

The simplest way to investigate this type of scenario is to fire up two instances of Query Analyzer.  Use the two instances to simulate two users.

start a transaction and then use  the UPDLOCK hint to use an update lock rather than a shared lock while reading the row.  
For more information on this query hint, see the Locking Hints topic in SQL Server Books OnLine.  

Here's a link to the topic:

http://msdn.microsoft.com/en-us/library/ms172398(v=sql.90).aspx


Regards
  Anoop S replied to Tahir khan
19-Jan-12 01:41 AM
You can use Update (U) lock mode->Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

refer this for more details
http://msdn.microsoft.com/en-us/library/aa213039%28v=sql.80%29.aspx
http://msdn.microsoft.com/en-us/library/Aa213026
  D Company replied to D Company
19-Jan-12 01:43 AM
Hello tahir,

In more descriptive way you can do the things like this.

In detail you can either use the lock keyword,apply lock over a private object and put the code which accesses the file within that lock..

lock(obj)
{
//code here

}
The lock keyword marks a statement block as a critical section by obtaining the mutual-exclusion lock for a given object, executing a statement(your code section), and then releasing the lock. This statement takes the

following form:

this will synchronize betweenn threads as a process

But in order to ensure sync between  threads of multiple processes..i.e. if say users from different processes can access this file

then you shud use Mutex

//create mutex object.

then mutex.acquiremutex()
then
// ur code to access file and

then to release mutex

Like below sample


class Test
{
    // Create a new Mutex. The creating thread does not own the
    // Mutex.
    private static Mutex mut = new Mutex();
    private const int numIterations = 1;
    private const int numThreads = 3;

    static void Main()
    {
      // Create the threads that will use the protected resource.
      for(int i = 0; i < numThreads; i++)
      {
        Thread myThread = new Thread(new ThreadStart(MyThreadProc));
        myThread.Name = String.Format("Thread{0}", i + 1);
        myThread.Start();
      }

      // The main thread exits, but the application continues to
      // run until all foreground threads have exited.
    }

    private static void MyThreadProc()
    {
      for(int i = 0; i < numIterations; i++)
      {
        UseResource();
      }
    }

    // This method represents a resource that must be synchronized
    // so that only one thread at a time can enter.
    private static void UseResource()
    {
      // Wait until it is safe to enter.
      mut.WaitOne();

      Console.WriteLine("{0} has entered the protected area",
        Thread.CurrentThread.Name);

      // Place code to access non-reentrant resources here.

      // Simulate some work.
      Thread.Sleep(500);

      Console.WriteLine("{0} is leaving the protected area\r\n",
        Thread.CurrentThread.Name);

      // Release the Mutex.
      mut.ReleaseMutex();
    }
}


 Hope this will help you !!


Regards
D
  D Company replied to Tahir khan
19-Jan-12 01:53 AM
Hello tahir,

In more descriptive way you can do the things like this.

In detail you can either use the lock keyword,apply lock over a private object and put the code which accesses the file within that lock..

lock(obj)
{
//code here

}
The lock keyword marks a statement block as a critical section by obtaining the mutual-exclusion lock for a given object, executing a statement(your code section), and then releasing the lock. This statement takes the

following form:

this will synchronize betweenn threads as a process

But in order to ensure sync between  threads of multiple processes..i.e. if say users from different processes can access this file

then you shud use Mutex

//create mutex object.

then mutex.acquiremutex()
then
// ur code to access file and

then to release mutex

Like below sample

class Test
{
    // Create a new Mutex. The creating thread does not own the
    // Mutex.
    private static Mutex mut = new Mutex();
    private const int numIterations = 1;
    private const int numThreads = 3;

    static void Main()
    {
    // Create the threads that will use the protected resource.
    for(int i = 0; i < numThreads; i++)
    {
      Thread myThread = new Thread(new ThreadStart(MyThreadProc));
      myThread.Name = String.Format("Thread{0}", i + 1);
      myThread.Start();
    }

    // The main thread exits, but the application continues to
    // run until all foreground threads have exited.
    }

    private static void MyThreadProc()
    {
    for(int i = 0; i < numIterations; i++)
    {
      UseResource();
    }
    }

    // This method represents a resource that must be synchronized
    // so that only one thread at a time can enter.
    private static void UseResource()
    {
    // Wait until it is safe to enter.
    mut.WaitOne();

    Console.WriteLine("{0} has entered the protected area",
      Thread.CurrentThread.Name);

    // Place code to access non-reentrant resources here.

    // Simulate some work.
    Thread.Sleep(500);

    Console.WriteLine("{0} is leaving the protected area\r\n",
      Thread.CurrentThread.Name);

    // Release the Mutex.
    mut.ReleaseMutex();
    }
}


 Hope this will help you !!


Regards
D
  Suchit shah replied to Tahir khan
19-Jan-12 01:50 PM
For the Update statement you can Lock the Record like below one

set transaction isolation level repeatable read
Begin Tran
Update customer set CustomerName='Changed' where CustomerCode='1001'
WAITFOR DELAY '000:00:50'
rollback tran


Personally i Would feel below articles will be the best for you for the explanation of locking concept just have a look on that

This article talks about 6 ways of doing locking in .NET. It starts with concurrency problems and then discusses about 3 ways of doing optimistic locking. As optimistic locking does not solve the concurrency issues from roots, it introduces pessimistic locking. It then moves ahead to explain how isolation levels can help us implement pessimistic locking. Each isolation level is explained with sample demonstration to make concepts clearer.

http://www.c-sharpcorner.com/uploadfile/shivprasadk/6-ways-of-doing-locking-in-net-pessimistic-and-optimistic/


Hope this much clear your concept still any doubt just let me know 
Create New Account
help
SQLXML Bulk Load "Error connecting to data source" SQL Server I've been struggling for days with this. I am using SQLXML 3.0 with SQL Server 2000. I have also created a runtime callable wrapper (Add Reference to the COM Object COMException was unhandled Message = "Error connecting to the data source." Source = "Microsoft XML Bulkload for SQL Server" ErrorCode = -2147467259 StackTrace: at SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class.Execute(String bstrSchemaFile, Object vDataFile) at ReportManager.DbUnitTest.BulkLoad at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * I have searched high and low, and I see no reason why my connection string new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class(); bulkLoadObj.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString; bulkLoadObj.Execute(schemaFile, dataFile); SQL Server XML Discussions SQL Server (1) ASP.NET (1) Exception (1) Bulk (1) Database (1
SQL Server Express 2005 "Timeout expired" SQL Server Hi! I'm working with XP Professional (SP3), Visual Basic 2008 Express and SQL Server 2005 Express (SP3). When running my project I get the "Timeout expired" message. This only unhandled Class = 14 ErrorCode = -2146232060 LineNumber = 65536 Message = "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will
WCF Service / SQL Server Connection issue SQL Server I have a WCF service hosted in IIS 6 on my development Server2003 machine that connects to it's sql server DB successfully. That same service won't connect to the same database on a target m also having a problem with a normal website on that same target connecting with SQL Server. So, I seems that stuff hosted in IIS on this Target server can't connect
IIS / SQL Server Problem IIS I have a WCF service hosted in IIS 6 on my development Server2003 machine that connects to it's sql server DB successfully. That same service won't connect to the same database on a target m also having a problem with a normal website on that same target connecting with SQL Server. So, I seems that stuff hosted in IIS on this Target server can't connect with sql server. What could cause this? The error is pasted below. Thanks, Gary System.ServiceModel.FaultException
Long running SQL query in ASP.Net SQL Server Greetings, I have to run a parametirzed sproc on SQL that can take 5-10 minutes to complete. The query returns a few megs of Any ideas on a quick and dirty way to do this without invoiking SSIS? Thanks SQL Server Discussions SQL Server (1) Outlook (1) Text (1) Database (1) You can use bcp but this requires xp_cmdshell