Performance problem case 02--sybase connection blocking problem

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

Investigation:

1. General system hangs the first thought of memory problems, but the phenomenon is loading, that is, there is no hanging, the thread is running, suspect that the thread is blocked. Configuration on JVISUALVM monitoring, after the problem memory is not full OK is not a memory problem, view thread dump found most of the SQL query is running,

The initial discovery is that running SQL is slow.

2. We used the Sybase database, ran a few simple SQL found a few minutes are not finished, using Sp_sysmon "00:00:30" monitoring nearly 30 seconds of the situation found that the CPU, memory, threads are no problem. Almost 1% of the utilization rate is not reached, suspected that a connection blocked the table, causing all the other connections caused by the blockage.

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

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

found that more than 10,000 locks, more than 7,000 locks, Ex_row-blk is blocked the other connection lock, found that there are 21 blocked the other connection lock, the corresponding table is T_ZXLD_SYYH, run select * from Master. sysprocesses find the appropriate connection, such as the user has 316/287/283, etc., the results such as the following:

At this point basically determine the reason, we use the C3P0 connection pool, should be a connection blocked the table, all the other connection query time is blocked, resulting in 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 blockage, found that tran_name are $chained_transaction, combined with the program inference, that is, in the operation of a chain of responsibility in the transaction is blocked, the system has 2 use of the chain of responsibility. Directly can infer the time of the approval of the responsibility chain caused, then the next step is to troubleshoot the code there is a problem. The approval process is as follows:

(1) Open transaction

(2) The main table data to be approved is queried according to the number of references, 2 SQL

(3) Approve the approval component (a component similar to the workflow) by invoking it. Approximately 5 updates per piece of data sql,2 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 change time and status of the data, and 1 update SQL per data

(8) Submit a transaction

The place where a reminder message is generated when the concurrency is suspected to be caused by blocking each other. All business tables are queried, assuming that other connections are approved in the transaction at this time. It will clog up. Other connections are generating messages that clog each other. Cause a deadlock. In theory, the database will take the initiative to handle the deadlock, but do not know what the reason, the number of deadlocks in the journal is not particularly large.

And the business process here is too reasonable, is often used by the user each batch approval about 100 data. So there are about 900 more SQL deletions. More than 200 query SQL, such as generating messages such as SQL, runs very slowly (due to the update of old messages involved). So many operations are slow to put into one transaction. Approval table about 3000w data, business Main Table 500w data, not run fast.

Workaround: Refactor the code here. Speak above run slow (5) (6) (7) Step a new thread, not put into a transaction to run, even if the failure has little effect.

This reduces SQL by 30% and is a lot faster. Updated to the site, observed for one weeks. The problem is solved by finding that there is no response.

Although the problem was solved, the root cause was not found. Why do they clog each other, why the deadlock did not take the initiative to detect, this is probably need to follow.

At last. Although the problem has been overcome, the middle communication has taken a lot of time. and operations personnel to the field data delayed very much, simple collation, perhaps a problem when collecting these data:
1, the result of Sp__lock. (Use the stored procedure provided at the end of the article)
2. Select * FROM Master. sysprocesses;
3, assume the database is slow. Plus the result of Sp_sysmon "00:00:30";
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