Blocking blocked and racblocked in RAC Environment

Blocking blocked and racblocked in RAC Environment

The blocking in the RAC environment is different from that in a single instance, because we need to consider the session in different instances. That is to say, the corresponding strain of the previously queried v $ session and v $ lock is global. This article provides two query scripts, and provides an example to demonstrate which sessions are blocked and which are blocked. For the concept of blocking and blocking in a single instance environment, see Oracle blocking (blocking blocked)

 

1. Demo Environment

Scott @ DEVDB> select * from v $ version where rownum <2; BANNER implements Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production -- publish SQL statements in scott session, scott @ DEVDB> begin 2 update emp set sal = sal + 100 where empno = 7788; 3 update dept set dname = 'dba 'where deptno = 10; 4 end; 5/PL/SQL procedure successfully completed. -- update the emp object leshami @ DEVDB> update scott in the leshami session. emp set sal = sal-200 where empno = 7788; -- update emp object usr1 @ DEVDB> update scott in usr1 session. dept set dname = 'dev' where deptno = 10;

2. Search for blocking

Scott @ DEVDB> @ inclusid_serial CONN_INSTANCE sid program osuser machine LOCK_TYPE LOCK_MODE CTIME OBJECT_NAME too far ------------------ ---- upper ------- upper -------------- lower Blocking-> '100 5' devdb1 20 sqlplus @ Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusi Ve 666 DEPTBlocking-> '100 5' devdb1 20 sqlplus @ Linux-01 (TNS V1-V3) oracle Linux-01 Transaction Exclusive 20,154 EMPWaiting '100 7' devdb1 49 sqlplus @ Linux-01 (TNS V1-V3) oracle Linux-01 Transaction None 618 EMPWaiting '933,116 91 'devdb2 933 sqlplus @ Linux-02 (TNS V1-V3) oracle Linux-02 Transaction None 558 DEPT -- through the above script we can see that session '123456' locks the object DEPT and EMP, and at this time the session '12347' and '1234569' are in the waiting state. -- The following is another way to get the blocking situation scott @ DEVDB> @ block_session_rac2BLOCKING_STATUS into SCOTT @ Linux-01 (INST = 1 SID = 20 Serail # = 1545) is blocking USR1 @ Linux-02 (INST = 2 SID = 933 Serial # = 11691) SCOTT @ Linux-01 (INST = 1 SID = 20 Serail # = 1545) is blocking leshami @ Linux-01 (INST = 1 SID = 49 Serial # = 1007) -- Author: Leshami -- Blog: http://blog.csdn.net/leshami

3. scripts used in the demo

[oracle@Linux-01 ~]$ more block_session_rac.sql set linesize 180col user_status format a15col sid_serial format  a15col program format a30 wrappedcol machine format a15 wrappedcol osuser format a15 wrappedcol conn_instance format a15col object_name format a25 wrapped SELECT DECODE (l.block, 0, 'Waiting', 'Blocking ->') user_status,         CHR (39) || s.sid || ',' || s.serial# || CHR (39) sid_serial,         (SELECT instance_name            FROM gv$instance           WHERE inst_id = l.inst_id)            conn_instance,         s.sid,         s.program,         s.osuser,         s.machine,         DECODE (l.TYPE,                 'RT', 'Redo Log Buffer',                 'TD', 'Dictionary',                 'TM', 'DML',                 'TS', 'Temp Segments',                 'TX', 'Transaction',                 'UL', 'User',                 'RW', 'Row Wait',                 l.TYPE)            lock_type--,id1                     --,id2         ,         DECODE (l.lmode,                 0, 'None',                 1, 'Null',                 2, 'Row Share',                 3, 'Row Excl.',                 4, 'Share',                 5, 'S/Row Excl.',                 6, 'Exclusive',                 LTRIM (TO_CHAR (lmode, '990')))            lock_mode,         ctime--,DECODE(l.BLOCK, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') lock_status         ,         object_name    FROM gv$lock l         JOIN gv$session s ON (l.inst_id = s.inst_id AND l.sid = s.sid)         JOIN gv$locked_object o            ON (o.inst_id = s.inst_id AND s.sid = o.session_id)         JOIN dba_objects d ON (d.object_id = o.object_id)   WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2, TYPE                                      FROM gv$lock                                     WHERE request > 0)ORDER BY id1, id2, ctime DESC;[oracle@Linux-01 ~]$ more block_session_rac2.sql SELECT DISTINCT          s1.username       || '@'       || s1.machine       || ' ( INST='       || s1.inst_id       || ' SID='       || s1.sid       || ' Serail#='       || s1.serial#       || ' ) IS BLOCKING '       || s2.username       || '@'       || s2.machine       || ' ( INST='       || s2.inst_id       || ' SID='       || s2.sid       || ' Serial#='       || s2.serial#       || ' ) '          AS blocking_status  FROM gv$lock l1,       gv$session s1,       gv$lock l2,       gv$session s2 WHERE     s1.sid = l1.sid       AND s2.sid = l2.sid       AND s1.inst_id = l1.inst_id       AND s2.inst_id = l2.inst_id       AND l1.block > 0       AND l2.request > 0       AND l1.id1 = l2.id1       AND l1.id2 = l2.id2;

 

More references

DML Error Logging

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % FOUND)

Batch SQL FORALL statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution PLAN

What is the Non-Blocking switch structure?

Non-blocking configuration this design aims to: the total bandwidth of all the switching ports in the chassis, which is lower than or equal to the forwarding capability of the switching structure, so that in any case, data streams are not blocked when they enter the switching structure. Therefore, non-blocking network devices are suitable for trunk connections. When selecting a trunk device, you only need to pay attention to the matching of the port density of the interface module and the forwarding capability of the switch structure. (recommended: when constructing a high-performance trunk network, you must select the trunk device of the non-blocking configuration ).
Learn together when answering questions!
Reference: www.chinaitlab.com/..42625024

The error code is 10004: the blocking process is canceled by WSACancelBlockingCall (). How can this error be solved?

Actually, it is to operate on the closed resources.

(Switch) today, when debugging network communication, we encountered an error: 10004 a blocking operation was interrupted by the call to WSACancelBlockingCall. The error occurs as follows:
Server:
1. A thread starts a socket1 listener and receives the incoming client connection.
2. at the same time, start a socket2 as client and connect to another MessageServer. Today, because MessageServer is not started, the socket2 connection fails. closesocket (socket2) is called, but socket2 is not set to INVALID_SOCKET.
3. A client is connected to socket1, receives a packet, and then calls socket2 to send a message to MessageServer. Moreover, the sending function does not determine whether socket2 is connected, send is called directly. Of course, this call fails. After the call fails, closesocket (socket2) is called. After this call, the accept function in the listening thread starts.
An error occurred. The error is "10004 a blocking operation is interrupted by the call to WSACancelBlockingCall ".
Final Solution and discovery:
Socket1 is created after socket2.
After socket2 is created, the value is 5580. After it is first created by closesocket (socket2), socket1 is created. This 5580 is obtained by socket1 and the value of socket1 is 5580. After receiving a connection, socket1 calls closesocket (socket2) again after the data fails to be sent in step 3. closesocket (5580) is actually called at this time ), therefore, the accept operation fails and error 10004 is displayed.
Note: After closesocket is called, you must immediately assign the socket value to INVALID_SOCKET. More importantly, do not call closesocket for multiple times.