Performance problem case 02--sybase connection blocking problem

Phenomenon: Recent field feedback a problem, the system at the time of approval, often stuck, the whole system is completely useless, browser access is in the state of loading.

Investigation:

1. General system hangs the first thought of memory problems, but the phenomenon is loading, that is, there is no hanging, thread is executing, suspect that the thread is blocked, configuration on the JVISUALVM monitoring, the problem after the memory is not full OK is not a memory problem, View Thread dump Discovery Most of the SQL queries are executing,

The initial discovery is slow execution of SQL.

2. We use the Sybase database, executed a few simple SQL discovery for a few minutes, using Sp_sysmon "00:00:30" monitoring for nearly 30 seconds, found that the CPU, memory, threads are no problem, almost 1% usage is not reached, Suspect that a connection is blocking the table, causing all other connections to block.

3. We use our own stored procedures to view blocked connections, with the following results:

PS: Use the sp_lock command to see which connection is blocking the database, but the display is tableid and so on, but also need to re-query into a specific table name, etc., the stored procedure is only converted here, the following will be attached.

found that more than 10,000 locks, more than 7,000 exclusive locks, ex_row-blk is blocking the other connections of the lock, found that there are 21 blocked other connected locks, the corresponding table is T_ZXLD_SYYH, execute select * from Master. sysprocesses find the corresponding connection, such as the user has 316/287/283, and so on, the result is as follows:

At this point basically determine the reason, we use the C3P0 connection pool, should be a connection blocking the table, all other connection queries are blocked, causing the connection pool is full, all requests involving database queries are blocked, the page is always in the loading state.

4. Then the next step is to find the blocking place, found that tran_name are $chained_transaction, combined with the program to judge, that is, in the implementation of a chain of responsibility within the transaction block, the System 2 used the chain of responsibility, Directly can be judged by the time of the approval of the responsibility chain, then the next step is to troubleshoot the code here, the approval process is as follows:

(1) Open transaction

(2) Query the main table data to be approved according to the parameters, 2 SQL

(3) Each call approval component (similar to a component of workflow) approval, about 5 updates per piece of data sql,2 a query SQL

(4) Update the main table status, 1 update SQL per piece of data

(5) Insert audit log, 1 Insert SQL per piece of data

(6) generate a reminder message, query All main table data, 20 queries sql,5 update Delete SQL (very slow)

(7) Update the incremental record table, record the data modification time and status, etc., each data 1 update SQL

(8) Submit a transaction

Suspected concurrency is caused by mutual blocking, the place where the reminder message is generated, the entire business table will be queried, if there are other connections in the transaction approval, will be blocked, other connections in the generation of messages, blocking each other, causing deadlocks. In theory, the database will automatically handle deadlocks, but for some reason, the number of deadlocks in the journal is not particularly large.

And the business process here is too reasonable, normal users each batch approval of about 100 data, so there are about 900 more deletions sql,200 multiple query SQL, such as generating messages, such as SQL execution is very slow (because it involves updating old messages), so many operations in a transaction is very slow, Approval table about 3000w data, business Main Table 500w data, execution is not very fast.

Workaround: Refactor the code here to say that the above execution slow (5) (6) (7) Step a new thread, not put into the transaction execution, even if the failure has little effect. This reduces SQL by 30%, a lot faster, updated to the scene, observed for one weeks, found no response to the situation, is considered a problem solving.

Although the problem is solved, but the root cause is not found, why will block each other, why the deadlock is not automatically detected, this is a follow-up need to follow.

Finally, although the problem has been solved, but the intermediate communication took a lot of time, and operation and maintenance personnel to the field data delayed a lot of things, simple collation, follow-up problems when collecting these data:
1. Results of Sp__lock; (using the stored procedure provided at the end of the article)
2. Select * FROM Master. sysprocesses;
3, if the database is slow, plus sp_sysmon "00:00:30" results;
4, middleware thread dump file;

Attach the stored procedure for Sybase view lock

IF object_id (' Dbo.sp__lock ') is not nulldrop PROCEDURE dbo.sp__lockgocreate PROCEDURE sp__lock (@dbname char (+) =null, @spid Int=null, @dont_format char (1) = NULL) Asbegin declare @dbid smallint if @dbname is not NULL SELECT @ DBID=DB_ID (@dbname) if (charindex ("Sa_role", Show_role ()) > 0) BEGIN if @dont_format is null SE Lect "Type" =substring (v.name,1,11), "User" =substring (Suser_name (P.suid) + "(" +rtrim (CONVERT (char (6), l.spid)) + ")", 1,20), "Table" =substring (db_name (l.dbid) + "..." +convert (char (), object_name (L.id,l.dbid)), 1,26), "page" =convert (char (8), l.page), "CMD" =sub String (p.cmd,1,11) from master: Syslocks L, Master. sysprocesses p, master.                Spt_values v where p.spid=l.spid and l.type = v.number and V.type = "L" and P.dbid=isnull (@dbid, P.dbid) and P.spid=isnull (@spid, P.spid) and L.dbid=isnull (@dbid, L.dbid) and L.spid=isnull (@spid, l.spid) Order by L.dbid, L.id, V.name Else Select "Type" =v.name, "User" =suser_name (p.suid) + "(" +rtrim (Convert (cha R (6), l.spid)) + ")", "Table" =db_name (l.dbid) + "..." +object_name (l.id,l.dbid), "page" =l.page, "CMD" =p.cmd from master: Syslocks L, Master. sysprocesses p, master.                Spt_values v where p.spid=l.spid and l.type = v.number and V.type = "L" and P.dbid=isnull (@dbid, P.dbid) and P.spid=isnull (@spid, P.spid) and L.dbid=isnull (@dbi    D,l.dbid) and L.spid=isnull (@spid, l.spid) Order by L.dbid, L.id, v.name return end        Select "Type" =v.name, "usernm" =convert (varchar), suser_name (P.suid) + "(" +rtrim (CONVERT (char (6), l.spid) + ")"), "Tablenm" =convert(varchar), db_name (l.dbid) + ".."), "page" =l.page, "CMD" =p.cmd, L.id, l.dbid into #locks From Master. Syslocks L, Master. sysprocesses p, master. Spt_values v where p.spid=l.spid and l.type = v.number and V.type = "L" and L.dbid=isnull (@d Bid,l.dbid) and L.spid=isnull (@spid, L.spid) and P.dbid=isnull (@dbid, P.dbid) and P.spid=isnull (@spid, p  . spid) Update #locks set Tablenm=tablenm+object_name (id,dbid) where dbid=db_id () or dbid=1 or dbid=2 update #locks set Tablenm=tablenm+convert (Varchar,id) where dbid<>db_id () and dbid>2 delete #locks where Ta Blenm like "tempdb.            #locks% "If @dont_format is null for select substring (Type, 1,11)," User "=substring (usernm, 1,14),        "Table" =convert (char (+), tablenm), "page" =convert (char (8), page), "CMD" =substring (cmd,1,11) From #locks order by dbid, ID, Type elsE select Type, "User" =usernm, "Table" =tablenm, page, CMD from #locks order by dbid, ID, Typeendgo 

Performance problem case 02--sybase connection blocking problem