| Author |
Topic |
|
global_erp_solution
Starting Member
28 Posts |
 Posted - 2012-09-18 : 09:16:47
|
| Hi,I have problem understanding lock and latch in SQL Server 2008. Let's say I have 3 tables:OrderOrderDetailsBillingHere are my questions:1. all three tables are LOCKED when ordering that would cause others to wait when performing queries on those 3 tables. Is this understanding correct?2. where is the LATCH in this operation?3. I'm pretty sure the entire table is LOCKED, or is it just the rows affected by the operation that are being LOCKED?4. will it be different for LOCKING mechanism when modifying or deleting existing order? what about reading from table (the LOCK and LATCH)?thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-09-18 : 10:08:09
|
| 1. By default, only write operations (INSERT/UPDATE/DELETE) would cause blocking, reads (SELECT) would be allowed. Locks are still acquired, these would be shared locks unless you override this using explicit transactions, and apply finer control using transaction isolation levels or various query hints.2. Latches are taken on physical database structures (pages, extents), you don't have direct control over these.3. By default SQL Server will use page-level locks. You can control this through various hints in your query, but this is not recommended unless detailed testing shows the hint has measurable benefit.4. See above items. |
 |
|
|
global_erp_solution
Starting Member
28 Posts |
Posted - 2012-09-18 : 11:23:00
|
| ^^answer number 3 --> by page-level, that would mean only the affected row is locked, right? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-09-18 : 12:19:14
|
| No. A page lock will lock the entire page in memory, and by extension, all the rows stored on that page. Rowlocks are done through query hints or modifying index properties. Again, using these hints are not recommended without definitive testing that shows they are beneficial.Locks are in-memory structures, and having too many rowlocks will negatively impact performance by using too much memory and too much CPU to manage them. Pagelocks are a good default, and if too many locks are generated then SQL Server will automatically escalate to a table lock.You can read more about SQL Server's locking mechanisms here: http://goo.gl/Cvlye You should also read about row versioning and snapshot isolation, as they resolve many common locking issues. |
|
|
|
global_erp_solution
Starting Member
28 Posts |
Posted - 2012-09-18 : 12:45:49
|
| I'm sorry, but are we talking about the same "page" here? in my understanding, 1 row occupies 1 page and one page has max size of 8KB. 8 pages will then occupy one extent. Is this the page you're talking about? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-09-18 : 12:54:51
|
| A page is always 8K in size. 8 contiguous pages make up an extent. Unless a row exceeds 4K then multiple rows can be stored on a page. I believe the limit is about 600 rows per page due to row overhead.There's more information on pages and extents here:http://goo.gl/7EdsBhttp://www.sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-Anatomy-of-a-page.aspx |
|
|
|
global_erp_solution
Starting Member
28 Posts |
Posted - 2012-09-20 : 23:28:33
|
| can anybody give me a link on isolation levels and the sample stored procedure for each isolation levels? thanks. I'm using SQL Server 2008 R2. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-09-21 : 06:55:30
|
| http://beyondrelational.com/modules/2/blogs/28/posts/10463/introduction-to-sql-server-transaction-isolation-levels.aspx--Chandu |
|
|
|
global_erp_solution
Starting Member
28 Posts |
Posted - 2012-09-21 : 10:00:22
|
| I have several questions, and for references, I use previous posts and this link given by bandi:http://beyondrelational.com/modules/2/blogs/28/posts/10464/sql-server-transaction-isolation-level-read-committed.aspxaccording to that blog, SQL by default uses READ COMMITTED isolation level. robvolk told me that by default, it's page-level locking and if the rows don't exceed 4K, multiple row WILL occupy the same page.1. my understanding of READ COMMITTED : READ COMMITTED simply means that any SELECT that contains locked row will get blocked altogether. is this correct?2. in my case, it's quite rare a row exceeds 4K, so according to robvolk, it's highly possible for one page to contain multiple rows in my DB design. let's say I issue a SELECT statement which contains rows from page A where one row in page A is being locked by another thread, does this mean the SELECT will get blocked altogether?3. the blog states that if PK exists in the table, SELECT query against unlocked row will ALWAYS immediately return the result. is this correct? consider this scenario, the target row is not locked, but another row that occupies the same page as the target row is being locked by another thread. will the SELECT get blocked? the explanation in that blog seems to contradict robvolk's explanation. or am I missing something here?thanks |
|
|
|
|
lock and latch