{"id":2145,"date":"2018-08-24T12:15:03","date_gmt":"2018-08-24T12:15:03","guid":{"rendered":"http:\/\/dbtut.com\/?p=2145"},"modified":"2021-02-08T12:03:31","modified_gmt":"2021-02-08T12:03:31","slug":"sql-server-lock-types","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/","title":{"rendered":"SQL Server Lock Types"},"content":{"rendered":"

 <\/p>\n

SQL Server implements different types of locks that determine how concurrent query requests access the data. For example, imagine that one user wants to read the same record, the other wants to change it.<\/p>\n

Will one be able to read the other while trying to change? Or will one change the other while reading?<\/p>\n

You can find detailed information about lock types in the table below. The behavior of the lock types depends on the isolation level. Details of the Isolation Levels can be found in the following articles.<\/p>\n

 <\/p>\n

Isolation Levels 1<\/a><\/p>\n

Isolation Levels 2<\/a><\/p>\n

Isolation Levels 3<\/a><\/p>\n

 <\/p>\n

You can access detailed information on how lock types are prevented by others in the article named “Lock Compatibility\u00a0On SQL Server<\/a>“.<\/p>\n

\n\n\n\n\n\n\n\n\n\n
Shared (S) Lock<\/td>\nIt is just the kind of lock that is locked while reading data. For example Select.<\/p>\n

The lock is released as soon as the data reading is finished.<\/p>\n

I can say the most innocent lock variety. Usually, nobody even feels that this lock is put down.<\/p>\n

In “pessimistic concurrency control”, Shared Lock allows a record to be selected by more than one concurrent session.<\/p>\n

You may want to read the article “Optimistic and Pessimistic Concurrency Control<\/a>“.<\/td>\n<\/tr>\n

Update (U) Locks<\/td>\nIt is a kind of lock that is put in place to prevent the deadlock problem.<\/p>\n

At the same time, “Update Lock” can be placed in the same resource only by one transaction.<\/p>\n

Transaction will update the Update Lock to Exclusive Lock when updating.<\/td>\n<\/tr>\n

Exclusive (X) Locks<\/td>\nIt is the type of lock that completely locks.<\/p>\n

When this lock is set, no other transaction can change that resource.<\/p>\n

Only select clauses that use the WITH (NOLOCK) hint can access this resource.<\/p>\n

WITH (NOLOCK) is a hint that is placed at the end of select statements and allows the select’s isolation level to behave like READ UNCOMMITTED.<\/p>\n

You can learn about the Isolation Levels from the links I shared at the beginning of the article.<\/td>\n<\/tr>\n

Intent Locks<\/td>\nWe have already told you that if a lock exists in one resource and another transaction wants to lock it, but the lock type of the first transaction that puts the lock is not compatible with the lock type of the second transaction, then the second transaction must wait for the first lock to finish.<\/p>\n

But while waiting, he puts an intent lock on the source. So it owns the next lock.<\/p>\n

And it prevents other transactions from locking the resource.<\/p>\n

There are several types of Intent Lock:<\/strong><\/p>\n

Intent Shared (IS):<\/strong> It is the type of lock that indicates that you are reading some of the source that is currently in the locked state. When the lock is over, it puts the Shared Lock on the source and reads the data it needs. It does not remove Intent Shared Lock when it puts Shared Lock. When the job is finished, two locks will be removed.<\/p>\n

Intent Exclusive (IX):<\/strong> It is the type of lock that indicates that you are going to change some of the source that is currently in the locked state. When the lock is over, it puts the Exclusive Lock on the source and change the data it needs.It does not remove Intent Exclusive Lock when it puts Exclusive Lock. When the job is finished, two locks will be removed.<\/p>\n

Shared With Intent Exlusive (SIX):<\/strong> It is the type of lock that indicates that the source that is currently in the locked state will read the entirety and change some of it.<\/td>\n<\/tr>\n

Schema<\/td>\nThe lock type is set when there is an operation on the schema.<\/td>\n<\/tr>\n
Bulk Update (BU)<\/td>\nBU is the lock type that is set when bulk copying is done with tablock hint.<\/td>\n<\/tr>\n
Key-range<\/td>\nWhen serializable transaction isolation level is used, the lock type that locks the range of rows that a select fetches when a query selects.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

 <\/p>\n

 <\/p>\n

You can see the locks on the database with the command below. In Request Status, it writes GRANT or WAIT.<\/p>\n

If the GRANT is written, then the transaction has the lock. If WAIT is written, it means waiting to put the lock.<\/p>\n

SELECT OBJECT_NAME(p.OBJECT_ID) AS TableName,\r\nresource_type, resource_description,request_mode,request_status,request_session_id\r\nFROM sys.dm_tran_locks l\r\nJOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id\r\nWHERE l.resource_database_id = DB_ID()<\/pre>\n\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
  SQL Server implements different types of locks that determine how concurrent query requests access the data. For example, imagine that one user wants to read the same record, the other wants to change it. Will one be able to read the other while trying to change? Or will one change the other while reading? …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[],"class_list":["post-2145","post","type-post","status-publish","format-standard","","category-mssql"],"aioseo_notices":[],"yoast_head":"\nSQL Server Lock Types - Database Tutorials<\/title>\n<meta name=\"description\" content=\"SQL Server Lock Types\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Lock Types - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"SQL Server Lock Types\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-24T12:15:03+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-02-08T12:03:31+00:00\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"SQL Server Lock Types\",\"datePublished\":\"2018-08-24T12:15:03+00:00\",\"dateModified\":\"2021-02-08T12:03:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/\"},\"wordCount\":693,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/\",\"name\":\"SQL Server Lock Types - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"datePublished\":\"2018-08-24T12:15:03+00:00\",\"dateModified\":\"2021-02-08T12:03:31+00:00\",\"description\":\"SQL Server Lock Types\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Lock Types\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server Lock Types - Database Tutorials","description":"SQL Server Lock Types","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Lock Types - Database Tutorials","og_description":"SQL Server Lock Types","og_url":"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/","og_site_name":"Database Tutorials","article_published_time":"2018-08-24T12:15:03+00:00","article_modified_time":"2021-02-08T12:03:31+00:00","author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"SQL Server Lock Types","datePublished":"2018-08-24T12:15:03+00:00","dateModified":"2021-02-08T12:03:31+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/"},"wordCount":693,"commentCount":1,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/","url":"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/","name":"SQL Server Lock Types - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"datePublished":"2018-08-24T12:15:03+00:00","dateModified":"2021-02-08T12:03:31+00:00","description":"SQL Server Lock Types","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2018\/08\/24\/sql-server-lock-types\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"SQL Server Lock Types"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/2145","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=2145"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/2145\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=2145"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=2145"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=2145"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}