Post reply

Database Locking Page Locking

  • April 6, 2006 at 9:06 pm

    #114263

    Dear all,

    Yesterday one of my OLAP DBA executed a job which perform ETL processes from Production Server to OLAP Server.After a couple of mins the result was very depressing.

    No user can connect to the Production server and those who are connected can do anything as the tables used in ETL processes are locked.

    The result is as below (Each table locked is of approx 2 GB)

     

    97921390112472PAG1:3507826       UGRANT
    97921390112472PAG1:3507825       UGRANT
    97921390112472PAG1:3515962       UGRANT
    97921390112472PAG1:3532459       UGRANT
    97921390112472PAG1:3515961       UGRANT
    97921390112472PAG1:3532456       UGRANT
    97921390112472PAG1:3511893       UGRANT
    97921390112472PAG1:3515960       UGRANT
    97921390112472PAG1:3532457       UGRANT
    97921390112472PAG1:3507824       UGRANT
    97921390112472PAG1:3511892       UGRANT
    97921390112472PAG1:3515963       UGRANT
    97921390112472PAG1:3511894       UGRANT
    97900DB                SGRANT
    97921390112472PAG1:3511888       UGRANT
    97921390112472PAG1:3515965       UGRANT
    97921390112472PAG1:3532460       UGRANT
    97921390112472PAG1:3507829       UGRANT
    97921390112472PAG1:3511889       UGRANT
    97921390112472PAG1:3515966       UGRANT
    97921390112472PAG1:3532463       UGRANT
    97921390112472PAG1:3507830       UGRANT
    97921390112472PAG1:3532458       UGRANT
    97921390112472PAG1:3507827       UGRANT
    97921390112472PAG1:3511895       UGRANT
    97921390112472PAG1:3515964       UGRANT
    97921390112472PAG1:3532461       UGRANT
    97921390112472PAG1:3507828       UGRANT
    97921390112472PAG1:3515928       UGRANT
    97921390112472PAG1:3528421       UGRANT
    97921390112472PAG1:3515929       UGRANT
    97921390112472PAG1:3515943       UGRANT
    97921390112472PAG1:3539328       UGRANT
    97921390112472PAG1:3515941       UGRANT
    97921390112472PAG1:3539330       UGRANT
    97921390112472PAG1:3515942       UGRANT
    97921390112472PAG1:3539329       UGRANT
    97921390112472PAG1:3547608       UGRANT
    97921390112472PAG1:3515938       UGRANT
    97921390112472PAG1:3539333       UGRANT
    97921390112472PAG1:3515939       UGRANT
    97921390112472PAG1:3539332       UGRANT
    97921390112472PAG1:3515940       UGRANT
    97921390112472PAG1:3539331       UGRANT
    97921390112472PAG1:3524339       UGRANT
    97921390112472PAG1:3507810       UGRANT
    97921390112472PAG1:3524338       UGRANT
    97921390112472PAG1:3507811       UGRANT
    97921390112472PAG1:3524341       UGRANT
    97921390112472PAG1:3507812       UGRANT
    97921390112472PAG1:3524340       UGRANT
    97921390112472PAG1:3507813       UGRANT
    97921390112472PAG1:3524343       UGRANT
    97921390112472PAG1:3507814       UGRANT
    97921390112472PAG1:3524342       UGRANT
    97921390112472PAG1:3507815       UGRANT
    97921390112472PAG1:3515936       UGRANT
    97921390112472PAG1:3539335       UGRANT
    97921390112472PAG1:3515937       UGRANT
    97921390112472PAG1:3539334       UGRANT
    97921390112472PAG1:3511890       UGRANT
    97921390112472PAG1:3515967       UGRANT
    97921390112472PAG1:3532462       UGRANT
    97921390112472PAG1:3511891       UGRANT
    97921390112472PAG1:3515952       UGRANT
    97921390112472PAG1:3539351       UGRANT
    97921390112472PAG1:3547615       UGRANT
    97921390112472PAG1:3515953       UGRANT
    97910764071041PAG1:755169        IXGRANT
    97921390112472PAG1:3539350       UGRANT
    97921390112472PAG1:3547614       UGRANT
    97921390112472PAG1:3515954       UGRANT
    97921390112472PAG1:3539349       UGRANT
    97921390112472PAG1:3547613       UGRANT
    97921390112472PAG1:3515955       UGRANT

    Can anyone tell me why it happend like this as sql server should release the lock from the row after update.

    from

    Killer

  • April 6, 2006 at 9:16 pm

    #631399

    It will release the lock when the transaction is committed.  If you have an update that spans a large number of rows, the server maintains the locks on all of them until it knows it doesn't have to roll the whole update back.  If this is all being done in one transaction (or one SQL statement), the locks stay put until you're committed or rolled back.

    And then again, I might be wrong ...
    David Webb

  • April 7, 2006 at 12:08 pm

    #631569

    Well, in cases where the 'update' has to span and table scan across huge amount of data its better to split into individual batches.

    I believe your update runs through dozens of GB which obviously end up in lock..

    Get busy living ....or get busy dying....

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply