sybase 锁

Step 1:- Log in the dataserever using isql

isql -U<user_name> -S<server_name> -w999

Step 2:- Check for blocking spids

Cmd:

1>use master 
2>go
1>select spid,blocked from sysprocesses where blocked >0
2>go

Example:

1> select spid,blocked from sysprocesses where blocked >0
2> go
spid   blocked
------ -------
   177     627   177 : blocked spid, 627: blocking spid
   314     627  
   559     843   559 : blocked spid, 843: blocking spid
   778     843  
(4 rows affected)

Step3 :- If in above step if there is some blocked spids, you need to monitor the blocking spid ( which is blocking other process to run ie 627, 843)

If for the blocking spid physical io is not moving , we need to esclate to App Team, If its is moving we can wait for some time(10 -15 mins).

If blocking is not clearing in 10-15 mins , we need to esclate to APP team.

Cmd:

1>use master
2>go
1>select spid,cmd,physical_io from sysprocesses where spid=<blocking spid>
2>go

Example :

1> select spid,cmd,physical_io from sysprocesses where spid=627
2> go
spid   cmd              physical_io
------ ---------------- -----------
   627 REORG               25370412
(1 row affected)
1> select spid,cmd,physical_io from sysprocesses where spid=627
2> go
spid   cmd              physical_io
------ ---------------- -----------
   627 REORG               25370412 <----- Physical io is not moving
(1 row affected)

1> select spid,cmd,physical_io from sysprocesses where spid=843
2> go
spid   cmd              physical_io
------ ---------------- -----------
   843 INSERT                     0 
(1 row affected)
1> select spid,cmd,physical_io from sysprocesses where spid=843
2> go
spid   cmd              physical_io
------ ---------------- -----------
   843 INSERT                     0 <----- Physical io is not moving 

(1 row affected)

Step 4:- Now we need to find the detail of blocking spid as below

Cmd:

1>use master
2>go
1>select * from sysprocesses where spid=<blocking spid>
2>go
1>sp_who "<blocking spid>"
2>go
1>dbcc traceon(3604)
2>go
1>dbcc sqltext(<blocking spid>)
2>go
1>sp_showplan <blocing spid>,null,null,null
2>go
1>sp_lock <blocking spid>
2>go

Example :

I have colllected the detail for 843 , you need to find out detail for all the blocking spids.

1> select * from sysprocesses where spid=843
2> go
spid   kpid        enginenum   status       suid        hostname   program_name     hostprocess cmd              cpu
        physical_io memusage    blocked dbid   uid         gid
        tran_name                                                        time_blocked network_pktsz fid
        execlass                       priority   affinity                       id          stmtnum     linenum     origsuid
        block_xloid clientname                     clienthostname                 clientapplname                 sys_id
        ses_id      loggedindatetime           ipaddr
------ ----------- ----------- ------------ ----------- ---------- ---------------- ----------- ---------------- -----------
        ----------- ----------- ------- ------ ----------- -----------
        ---------------------------------------------------------------- ------------ ------------- ------
        ------------------------------ ---------- ------------------------------ ----------- ----------- ----------- -----------
        ----------- ------------------------------ ------------------------------ ------------------------------ ------
        ----------- -------------------------- ---------------
   843   283443444           4 lock sleep           546 nyggmgrotc RESETREP         23822       INSERT                     3
                  0          22     627      4         405       16390
        $user_transaction                                                       15143           512      0
        EC2                            HIGH       ANYENGINE                        637764804           5         439        NULL
                  0                                                                                                   0
                  0        Jun 12 2011 11:24AM 10.152.115.100

(1 row affected)

1> sp_who "843"
2> go
fid    spid   status       loginame                       origname                       hostname   blk_spid
        dbname                         cmd              block_xloid
------ ------ ------------ ------------------------------ ------------------------------ ---------- --------
        ------------------------------ ---------------- -----------
     0    843 lock sleep   lcprusr                        lcprusr                        nyggmgrotc 627
        summitdb                       INSERT                     0
(1 row affected)
(return status = 0)
1> dbcc traceon(3604)
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
1> dbcc sqltext(843)
2> go
SQL Text: 320110714CONV_AVG
AUTO      CARRY
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
1> sp_showplan 843,null,null,null
2> go
QUERY PLAN FOR STATEMENT 5 (at line 439).

   STEP 1
       The type of query is INSERT.
       The update mode is direct.
       TO TABLE
           dmASSET_HIST
       Using I/O Size 2 Kbytes for data pages.

(return status = 0)
1> sp_lock 843
2> go
The class column will display the cursor name for locks associated with a cursor for the current user and the cursor id for other

users.

fid    spid   loid        locktype                     table_id    page        row    dbname
        class                          context
------ ------ ----------- ---------------------------- ----------- ----------- ------ ---------------
        ------------------------------ ----------------------------
     0    843        1686 Ex_intent                      589764633           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                         589764633      199169      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent                      635720842           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                         635720842    22607248      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent                      667720956           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                         667720956    46143296      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent                     1076770418           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row-blk                    1076770418    13586806      2 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                        1076770418    13807873      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent                     1108770532           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                        1108770532    18465042      2 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                        1108770532    33477240      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent-blk                 1732772755           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                        1732772755    13475329      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent                     1764772869           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                        1764772869    19673089      0 summitdb
        Non Cursor Lock
(16 rows affected)
(return status = 0)

Step 5:- With above detail you need to send the mail to APP Team and need to do follwoup untill it is not released.

Category:  Sybase ASE Server