top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

What does mean of locking granularity in SQL Server?

+1 vote
899 views
What does mean of locking granularity in SQL Server?
posted Mar 6, 2015 by Amit Kumar Pandey

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote

Lock granularity specifies which resource is locked by a single lock attempt. The SQL Server Database Engine can lock the following resources:

Row,Page,Index key or range of index keys,Table, Extent etc

Lock granularity affects concurrency. In general, the more granular the lock, the more concurrency is reduced. This means that row-level locking maximizes concurrency because it leaves all but one row on the page unlocked. On the other hand, system overhead is increased because each locked row requires one lock. Page-level locking (and table-level locking) restricts the availability of data but decreases the system overhead.

answer Apr 21, 2015 by Shankar.p
Similar Questions
+1 vote

I have data in table A as below

Assetid   attribute   value
    1546    Ins_date   05062011
    1546    status     active
    1546    X          10.4567
    1546    Y          27.56
    1546    size       17
    675     X          4.778
    675     Y          53.676
    675     depth      5
    675     st_date    06092010

I have data as above in table A. This table has many Assetids 1546,675,....etc. attributes might vary for assets.

I want output as below:

assetid  ins_date  status  X        Y       Size  depth  st_date
1546     05062011  active  10.4567  27.56   17    null   null
675      null      null    4.778    53.676  null  5      06092010

I have created Stored procedure, then called in Informatica to achieve this output. However, since i have large volume of data, it is taking much time to load.

Please suggest me other easy and best way to load it.

...