locked
SQL Server Locking Mechanism... RRS feed

  • Question

  • lets say we have a table on disk. duing a transaction a single row will be modified. so that page will go in memory.

    Now as per locking SQL Server will apply IX lock on table level also while table is not inside the memory, then how SQL Server will manage locking for that table which in not inside memory (only a single page of that table is in the memory)?

    is this due to metadata of that table? then what will be the internal working for this?
    • Moved by Kalman Toth Friday, August 29, 2014 11:28 PM Better fit
    Friday, August 29, 2014 5:00 AM

Answers

  • As other said, Locking Manager maintains set of internal locking structures. Each locking structure references the object (table, page, row, etc) involved in the lock described by resource. For tables, that resource is object_id. For pages - it is address of the page in the database. For rows, it is a hash of the index key. Information about locking structures are exposed through sys.dm_tran_locks DMV - you can read documentation for the details.

    When SQL Server executes (and compiles) queries, it acquires table-level locks as the first step. Depend on the query, it could be intent, full or schema-level lock. SQL Server gets object_id for the table and checks if lock can be acquired (e.g. there is no other incompatible locks (lock structures) held for this table). No "table data pages" need to be read at this point to the buffer pool.

    When table-level lock is acquired, SQL Server starts to read data pages and acquire locks on the pages and rows if needed. It is not always happened, for example, SQL Server does not acquire shared (S) locks with read uncommitted isolation level. In any case, "acquiring" in that case means creating lock structures with resources reference required objects (pages, rows, etc). That reference is maintained only through lock structure attributes; there is no "physical" pointers in the lock structures.

    Usually, locks are acquired after data is read to the memory. This is done because SQL Server never works with data on-disk. It always read data to the memory first. However, as I already mentioned in the previous post in the thread, it is completely possible that data pages with active locks would flushed out of the buffer pool. Again, there is not physical links between lock structures and data.

    In real life, it is far more complicated process. SQL Server can perform lock escalation and release row- and page- level locks replacing them with table-level lock. You can have lock collision in case of wide composite indexes, when different rows generate the same hash (you can use %%lockres%% internal function to see the hash of the key):

    select %%lockres%%, * from T1

    You can have the situations when row-locks are not acquired and replaced by full page-level locks. And so on.

    The key point, you need to think about locks as about "logical" concept, which is a bit independent from physical data access. Type of the locks and granularity often depends on the execution plans. For example, in some cases, SQL Server can obtain update (U) locks during data modifications and replace them with exclusive (X) locks later. On the other cases, SQL Server would use exclusive (X) locks from the beginning without even bothering to acquire update (U) locks

    Read about lock compatibility, transaction isolation levels, optimistic locking (versioning), lock escalation and other related topics. It should be enough to understand how SQL Server works and how to troubleshoot concurrency issues in the system. I would suggest you again to check my blog as the starting point.

    Hope, it answers your questions.


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Saturday, August 30, 2014 2:17 PM

All replies

  • Locking is a logical concept - there are no physical locks that are acquired on the row/page/table. It doesn't matter if the page or all pages of a table are on disk or memory. The locking information is populated in syslockinfo pseduo table /sys.dm_tran_locks  view and it ensures that data consistency is maintained (two sessions don't overwrite each other)

    Satish Kartan http://www.sqlfood.com/

    • Proposed as answer by Olaf HelperMVP Friday, August 29, 2014 8:14 AM
    Friday, August 29, 2014 8:00 AM
  • Now as per locking SQL Server will apply IX lock on table level also while table is not inside the memory, then how SQL Server will manage locking for that table which in not inside memory (only a single page of that table is in the memory)?

    Hi,

    SQL Server will only apply lock on table/pages/rows when it has brought table/pages related to that physical table from disk into memory. Nothing is done on disk.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles

    Friday, August 29, 2014 8:34 AM
  • I wrote the set of blog posts about locking and concurrency model in SQL Server. It can provide you the overview how stuff works under the hood. You can read it at: http://aboutsqlserver.com/lockingblocking/

    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Friday, August 29, 2014 1:25 PM
  • Shanky_621 - That is not true, locking is just logical marking - it doesn't matter if the page/table is on disk or memory. SQL does not bring the table into memory just to take an IX lock - this intent lock is just a marker  so that another session cannot acquire an exclusive table lock - when a page belonging to a table is retrieved by a session, it will just mark an IX lock on the table (but doesn't bring the entire table in memory - there is no reason to do that)

    Satish Kartan http://www.sqlfood.com/

    Friday, August 29, 2014 1:27 PM
  • Shanky_621 - That is not true, locking is just logical marking - it doesn't matter if the page/table is on disk or memory.

    Satish Calm down, did anywhere I said it is not a physical concept. Now are you saying locks can be taken on disk, seems from what u wrote

    SQL does not bring the table into memory just to take an IX lock - this intent lock is just a marker  so that another session cannot acquire an exclusive table lock - /

    Yes I am very much aware about what Intent Exclusive means. Intent as a literal meaning to show the intent that in this table particular row or page is exclusively locked.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles

    Friday, August 29, 2014 1:40 PM
  • Shanky - Didn't mean to offend you but I just don't want to mislead others. Here is what you wrote that locking is only done when the table is brought in memory. This what you wrote, which is not correct

    "SQL Server will only apply lock on table/pages/rows when it has brought table/pages related to that physical table from disk into memory. Nothing is done on disk."


    Satish Kartan http://www.sqlfood.com/

    Friday, August 29, 2014 1:56 PM
  • "SQL Server will only apply lock on table/pages/rows when it has brought table/pages related to that physical table from disk into memory. Nothing is done on disk."


    Satish Kartan http://www.sqlfood.com/

    So what is incorrect in this. May be you are drawaing different inference from it.  I says if SQL server want to read  a page and may be update a row on that page. The page would be brought from disk into memory ( if not already present) and row lock will be applied. If SQL server finds that number of rows are high and more meory would go in taking row lock it would escalate it to page and if required to table.

    From MS doc something about latches a light form of locks

    Whenever data is written to or read from a page in the SQL Server buffer pool a worker thread must first acquire a buffer latch for the page. There are various buffer latch types available for accessing pages in the buffer pool including exclusive latch (PAGELATCH_EX) and shared latch (PAGELATCH_SH). When SQL Server attempts to access a page which is not already present in the buffer pool, an asynchronous I/O is posted to load the page into the buffer pool. If SQL Server needs to wait for the I/O subsystem to respond it will wait on an exclusive (PAGEIOLATCH_EX) or shared (PAGEIOLATCH_SH) I/O latch depending on the type of request; this is done to prevent another worker thread from loading the same page into the buffer pool with an incompatible latch. Latches are also used to protect access to internal memory structures other than buffer pool pages; these are known as Non-Buffer latches.

    But this whole operation of locking  happens in memory not on disk.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles


    Friday, August 29, 2014 2:13 PM
  • Shanky, you are correct that data modifications are done in-memory only so yes, locks can be acquired only when page/row are in the buffer pool.

    However, I am unaware of anything that prevents SQL Server from flushing dirty pages from uncommitted transaction to the disk (after log records were hardened) and remove those pages from the buffer pool. I expect it to be very rare situation and only happen when system has very low PLE, but I am sure it is possible. In the end, locking structures live separately from the data. 


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Friday, August 29, 2014 2:19 PM
  • Point is that IX lock on a table is taken just after retrieving a single page (no need to bring the entire table in memory for an IX lock as your post seems to point out). Perhaps it is just an interpretation issue. I am glad we cleared any misunderstanding

    Here is what you posted

    Now as per locking SQL Server will apply IX lock on table level also while table is not inside the memory, then how SQL Server will manage locking for that table which in not inside memory (only a single page of that table is in the memory)?

    Hi,

    SQL Server will only apply lock on table/pages/rows when it has brought table/pages related to that physical table from disk into memory. Nothing is done on disk.



    Satish Kartan http://www.sqlfood.com/

    Friday, August 29, 2014 2:22 PM
  • Point is that IX lock on a table is taken just after retrieving a single page (no need to bring the entire table in memory for an IX lock as your post seems to point out). 

    Intent (and/or schema stability) lock on the table is taken BEFORE any page/row access (and locks). If intent lock cannot be acquired, session is blocked.  

    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Friday, August 29, 2014 2:30 PM
  • Dimitri - You are correct the intent lock is taken before any key/page access. My point is that Shanky's post seem to indicate that the table has to be brought in memory even for taking an IX lock because a single key/page was accessed

    Satish Kartan http://www.sqlfood.com/

    Friday, August 29, 2014 2:37 PM
  •  Shanky's post seem to indicate that the table has to be brought in memory even for taking an IX lock because a single key/page was accessed

    Satish Kartan http://www.sqlfood.com/

    There is no cure for misinterpretation. I used (table/pages/rows) which does not means I am saying to read a page table would be brough in memory unless you use different type of english. I would like to stop this here as  we are going off topic. And dont use other name when you post wrong information.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles

    Friday, August 29, 2014 2:42 PM
  • Exactly, we don't want the readers of this forum to misinterpret. Let's not belabor the issue, now that it has been clarified

    Satish Kartan http://www.sqlfood.com/


    Friday, August 29, 2014 2:54 PM
  • As locking is a logical concept.My Question is still same here:

    how sql server manage this thing internally?
    please explain this with my example
    how SQL Server will manage locking for that table which in not inside memory?

    Friday, August 29, 2014 7:30 PM
  • As locking is a logical concept.My Question is still same here:

    how sql server manage this thing internally?
    please explain this with my example
    how SQL Server will manage locking for that table which in not inside memory?

    Please search on internet. We encourage people to ask constructive question not which has big answers and which would require a lot to explain.

    Did you referred to link posted by Dimitri. I guess it has lot of good info. If you want to read more you can refer to below book by JRJ and Jonathan Kehayias

    http://it-ebooks.info/book/1377/

    Please refer to chapter 6 Locking and Latches


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles

    Friday, August 29, 2014 8:12 PM
  • As locking is a logical concept.My Question is still same here:

    how sql server manage this thing internally?
    please explain this with my example
    how SQL Server will manage locking for that table which in not inside memory?

    SQL Server uses internal in-memory structures to maintain lock information.  These lock structures are maintained independently of the locked resource and never persisted to disk for performance reasons, even if the locked resource happens to be a disk-based object. 


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, August 29, 2014 10:53 PM
  • As other said, Locking Manager maintains set of internal locking structures. Each locking structure references the object (table, page, row, etc) involved in the lock described by resource. For tables, that resource is object_id. For pages - it is address of the page in the database. For rows, it is a hash of the index key. Information about locking structures are exposed through sys.dm_tran_locks DMV - you can read documentation for the details.

    When SQL Server executes (and compiles) queries, it acquires table-level locks as the first step. Depend on the query, it could be intent, full or schema-level lock. SQL Server gets object_id for the table and checks if lock can be acquired (e.g. there is no other incompatible locks (lock structures) held for this table). No "table data pages" need to be read at this point to the buffer pool.

    When table-level lock is acquired, SQL Server starts to read data pages and acquire locks on the pages and rows if needed. It is not always happened, for example, SQL Server does not acquire shared (S) locks with read uncommitted isolation level. In any case, "acquiring" in that case means creating lock structures with resources reference required objects (pages, rows, etc). That reference is maintained only through lock structure attributes; there is no "physical" pointers in the lock structures.

    Usually, locks are acquired after data is read to the memory. This is done because SQL Server never works with data on-disk. It always read data to the memory first. However, as I already mentioned in the previous post in the thread, it is completely possible that data pages with active locks would flushed out of the buffer pool. Again, there is not physical links between lock structures and data.

    In real life, it is far more complicated process. SQL Server can perform lock escalation and release row- and page- level locks replacing them with table-level lock. You can have lock collision in case of wide composite indexes, when different rows generate the same hash (you can use %%lockres%% internal function to see the hash of the key):

    select %%lockres%%, * from T1

    You can have the situations when row-locks are not acquired and replaced by full page-level locks. And so on.

    The key point, you need to think about locks as about "logical" concept, which is a bit independent from physical data access. Type of the locks and granularity often depends on the execution plans. For example, in some cases, SQL Server can obtain update (U) locks during data modifications and replace them with exclusive (X) locks later. On the other cases, SQL Server would use exclusive (X) locks from the beginning without even bothering to acquire update (U) locks

    Read about lock compatibility, transaction isolation levels, optimistic locking (versioning), lock escalation and other related topics. It should be enough to understand how SQL Server works and how to troubleshoot concurrency issues in the system. I would suggest you again to check my blog as the starting point.

    Hope, it answers your questions.


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Saturday, August 30, 2014 2:17 PM
  • Hi Dmitri,

    Your blogs are very clear and Excellent. i have read first 10 so far.

    Actually, i wants to know the internal working of that logical locking concept and what is the step by step algorithm that works for locking concept internally? 

    Saturday, August 30, 2014 6:20 PM
  • If SQL server finds that number of rows are high and more meory would go in taking row lock it would escalate it to page and if required to table.

    Hi Shanky,

    here you are not correct. If a lock escalation will occure it will ALWAYS lock the underlying table itself!

    "The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks. In SQL Server 2008, locking of partitioned tables can escalate to the HoBT level for the associated partition instead of to the table lock. A HoBT-level lock does not necessarily lock the aligned HoBTs for the partition."

    see details here: http://technet.microsoft.com/en-us/library/ms184286.aspx


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Sunday, August 31, 2014 8:39 AM