Day 12

Quiz

1:What is the difference between a shared and an exclusive lock?
A1: A shared lock, acquired during a read process, prevents other users from changing data, but permits other processes to read the data. An exclusive lock, acquired during a write process, prevents other users from reading or writing data.
2:What statements (select, insert, update, delete) will acquire a shared lock?
A2: All select statements acquire shared locks. Insert, update, and delete statements that rely on other tables will use shared locks to read from those tables in addition to exclusive locks on the affected table.
3:What is an intent lock, and what exactly gets locked?
A3: An intent lock is a bookmark that a process uses to tell other processes that it holds real locks within a resource. For example, a shared table-level intent lock means that the process holds page or key locks within the table. Other resources will be able to obtain their own page or key locks within the table, but they will not be able to get an exclusive table-level lock until the intent lock is cleared.
4:Does an exclusive intent lock block other users from reading or writing?
A4: No. An exclusive intent lock tells other processes that there is an exclusive lock within the resource. Other processes can read and write within the same resource. An exclusive intent lock will block table-level shared and exclusive locks until the intent lock is cleared.

Exercises

1:Write a query to retrieve a list of five worst-selling products. You'll want to update the products table when your query is complete, so get and retain exclusive locks on the low-selling products. (You will need to examine the locks in the next exercise.)
begin tran
select top 5
        p.ProductID,
        p.ProductName,
        sum(od.Quantity * od.UnitPrice * (1 - od.Discount))
              as 'Total Dollars'
from
        Products p with (xlock)
    inner join
        [Order Details] od
            on p.ProductID = od.ProductID
group by
        p.ProductID,
        p.ProductName
order by
        'Total Dollars'desc

A1: Results:
ProductID   ProductName                Total Dollars
----------- -------------------------- -------------------
         48 Chocolade                   1368.7125244140625
         33 Geitost                               1648.125
         15 Genen Shouyu                1784.8249969482422
         67 Laughing Lumberjack Lager   2396.8000183105469
         74 Longlife Tofu                           2432.5

2:Find out the spid of your process. 
select @@spidsp_lock 51

A2: This is the spid when I ran this exercise on my computer.
------
    51

Based on that process ID, I get a list of locks for that spid.

spid dbid ObjId     IndId  Type Resource     Mode     Status
---- ---- --------- ------ ---- ------------ -------- ------
  51    6         0      0 DB                S        GRANT
  51    6  17575457      0 TAB               IX       GRANT
  51    6  17575457      1 KEY  (3b00435f91  X        GRANT
  51    6  17575457      1 KEY  (3e0071af4f  X        GRANT
  51    6  17575457      1 KEY  (260001d0e2  X        GRANT
  51    6  17575457      1 KEY  (1d00a1afc8  X        GRANT
  51    6  17575457      1 PAG  1:276        IX       GRANT
  51    1  85575343      0 TAB               IS       GRANT
  51    6  17575457      1 KEY  (3c00fa6746  X        GRANT

3:In a new session, get a list of products in category 1. 
select
        ProductName
from
        Products
where
        CategoryID = 1

A3: Results:

There is no response because the Products table is being blocked by a transaction that has not been committed yet.

4:Find a blocking and a blocked session. Clear the locks from the blocking session.
sp_who

A4: Results: 
spid status     loginame    blk dbname     cmd
---- ---------- ----------- --- ---------- ----------------
   1 background sa          0   NULL       LAZY WRITER
   2 sleeping   sa          0   NULL       LOG WRITER
   3 background sa          0   master     SIGNAL HANDLER
   4 background sa          0   NULL       LOCK MONITOR
   5 background sa          0   master     TASK MANAGER
   6 sleeping   sa          0   NULL       CHECKPOINT SLEEP
   7 background sa          0   master     TASK MANAGER
   8 background sa          0   master     TASK MANAGER
   9 background sa          0   master     TASK MANAGER
  10 background sa          0   master     TASK MANAGER
  51 sleeping   northwind_d 0   Northwind  WAITING COMMAND
  52 sleeping   northwind_d 0   Northwind  WAITING COMMAND
  53 sleeping   northwind_d 51  Northwind  SELECT
  54 runnable   northwind_d 0   Northwind  SELECT

Session 51 is blocking session 53.

In session 53, type:

rollback transaction

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset