Explaing locking in SQL Server

In SQL Server locking can be done at various levels, the levels are as follows:
  • RID - this is used to lock a single row
  • Key - this is used to lock rows within an index
  • Page - this is used to lock a 8 KB data page or index page
  • Extent - this is used to lock contiguous 8 data pages or index pages
  • Table - this is used to lock a table including the data and index
  • Database - this is used to lock the full database
The various types of locking that are available are as follows:
  • Shared Lock (S)
    Shared lock is used for concurrent read operations (SELECT), once the read operation is done the lock is released unless the lock has been specified as a repeated process or a locking hint has been used to keep the lock till the duration of the transaction.
  • Update Lock (U)
    This type of lock is used on resources that can be updated and prevents the common type of deadlock where by two transactions are trying to update at the same time. Only one transaction can obtain an Update lock, if any changes are to be made then the lock is changed to Exclusive lock otherwise it is changed to Shared Lock.
  • Exclusive Lock (X)
    This type of lock is used to make sure that the data is not updated by multiple transactions at one point of time, this includes the (INSERT, UPDATE, DELETE) statements.
  • Intent Lock
    This type of lock is used to show the intent of getting a Shared or Exclusive lock at a lower level resource. The various types of intent locks that are available are as follows:
    • Intent Shared (IS)
      This is issued by a transaction when it wants to read some resources
    • Intent Exclusive (IX)
      This is issued by a transaction when it wants to update some resources
    • Shared with Intent Exclusive (SIX)
      This is issues by a transaction with the intention of reading some resources and then modify some of them
  • Schema Lock (Sch)
    This type of lock is used when changes to the schema of the table is done, the two types of schema locks that are there are as follows:
    • Schema-Modification Lock (Sch-M)
      This type of lock is acquired when DDL statements are being executed
    • Schema-Stability Lock (Sch-S)
      This type of lock is acquired when the query is being compiled, at this point no other transaction locks are blocked.
  • Bulk Update Lock (BU)
    This lock is used when data is being uploaded to a table in bulk.

Comments

Post a Comment

Popular posts from this blog

jQuery Basics

jQuery Introduction The files needed to run a jQuery function can be downloaded from the URL mentioned below: http://jquery.com/download/ There are actually two versions of the file that is available, the first one id the minified and compressed version, jquery-1.10.1.min.js , which is basically used for the production environment and the other is the full version, uncompressed and readable, which is used for the development environment. If somebody does not want to download the file, they can use it from the CDN (Content Delivery Network) provided both by Google and Microsoft, the details are here under: Google - http://ajax.googleapis.com/ajax/libs/jquery/1.10.1/jquery.min.js Microsoft - http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.1.min.js Note:  If you look at the Google URL above - the version of jQuery is specified in the URL (1.10.1). If you would like to use the latest version of jQuery, you can either remove a number from the end of the version st...
Read more

What is the difference between a Page Layout and Master Page in SharePoint?

A Page Layout is nothing but a template which when used in conjuncture with the Master Page gives the look and feel and contents of the page. Each page layout has an associated content type that determines the kind of content that can be stored on pages based on that page layout. Master Pages and Page Layouts dictate the overall look and feel of your SharePoint site. Master Pages contain controls that are shared across multiple page layouts, such as navigation, search, or language-preference for multilingual sites. Page layouts contain field controls and Web Parts. All page layouts reference a master page that is based on the CustomMasterUrl property of the SPWeb class. The top-level SharePoint Server site for a site collection hosted on SharePoint Server 2010 has a special document library called the Master Page and Page Layout Gallery. All Page Layouts and Master Pages are stored in this document library. Reference: http://blog.beckybertram.com/Lists/Posts/Post...
Read more

Accessing data from SharePoint 2010 to an ASP.NET application

Image
In order to access the SharePoint lists and document libraries data from a native ASP.NET application we need to make use of the web services that have been provided. The full list of the web services available from within SharePoint 2010 can be found here http://www.etechplanet.com/blog/sharepoint-web-services-7c-list-of-all-web-services-available-with-wss-30moss-2007.aspx The steps to access the web service are as follows: Create a native ASP.NET web application Add a reference of the web service Create an object of the web site Pass on to the web service object the credentials using which the access will be authorized Retrieve the results using the appropriate method
Read more