TX Enqueue in Mode 6的解决
作者 ：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2008/12/tx-enqueue-in-mode-6.html
链接：http://www.dbaroad.me/archives/2008/12/tx-enqueue-in-mode-6.html
Enqueue等待事件中，TYPE为TX，MODE为6的等待，应该是最为常见，解决也相对较为容易的一种。
出现这种类型的等待，一般都是通过找出堵住其它会话的进程，通知应用提交或回滚事物，或是强制杀掉进程来解决。
下面是一个的生产案例：
查看enqueue类型,发现为TX锁，MODE为6：
sys@DB1>@enq INST_ID SID SQL_HASH_VALUE EVENT P1 P1RAW TYPE MODE ------- -------- -------------- ------------- ---------- ---------------- ------ ----- 1 678 1961852720 enqueue 1415053318 0000000054580006 TX 6 1 708 1961852720 enqueue 1415053318 0000000054580006 TX 6 1 831 1961852720 enqueue 1415053318 0000000054580006 TX 6 2 469 1961852720 enqueue 1415053318 0000000054580006 TX 6 1 904 1961852720 enqueue 1415053318 0000000054580006 TX 6 1 874 1961852720 enqueue 1415053318 0000000054580006 TX 6 2 485 1961852720 enqueue 1415053318 0000000054580006 TX 6 2 736 1961852720 enqueue 1415053318 0000000054580006 TX 6 2 776 1961852720 enqueue 1415053318 0000000054580006 TX 6 2 752 1961852720 enqueue 1415053318 0000000054580006 TX 6 2 723 1961852720 enqueue 1415053318 0000000054580006 TX 6 1 159 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 177 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 236 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 721 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 810 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 844 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 191 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 68 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 1855 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 946 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 920 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 873 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 860 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 848 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 823 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 637 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 634 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 631 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 626 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 612 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 604 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 576 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 554 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 547 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 826 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 824 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 809 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 798 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 792 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 773 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 769 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 763 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 748 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 885 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 882 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 833 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 830 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 739 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 732 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 730 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 719 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 714 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 700 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 668 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 645 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 541 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 525 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 507 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 474 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 275 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 224 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 212 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 813 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 786 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 710 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 563 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 574 2218962563 enqueue 1415053318 0000000054580006 TX 6 1 604 2218962563 enqueue 1415053318 0000000054580006 TX 6 2 157 2680634301 enqueue 1415053318 0000000054580006 TX 6 2 743 2680634301 enqueue 1415053318 0000000054580006 TX 6 2 518 2680634301 enqueue 1415053318 0000000054580006 TX 6 |
找出堵住其它会话的进程：
sys@DB1>@lock ------------------------- Session cause the lock -------------------------- INST_ID SID SERIAL# USERNAME SQL_HASH_VALUE STATUS TYPE OSUSER MACHINE LMODE TYPE REQUEST CTIME ------- -------- ------- ---------- -------------- -------- ------ --------------- --------------- ------ ------ ------- ------ 2 1303 2684 USER1 0 INACTIVE USER yong.ou MSHOME\A2365DC3 6 TX 0 2393 ------------------------- Sessions about lock -------------------------- STATUS SID INST_ID CTIME ID1 ID2 LMODE REQUEST TYPE -------- -------- ------- ------ ---------- ---------- ------ ------- ------ Holder: 1303 2 2393 2687021 361395 6 0 TX Waiter: 68 2 488 2687021 361395 0 6 TX Waiter: 157 2 1156 2687021 361395 0 6 TX Waiter: 191 2 54 2687021 361395 0 6 TX Waiter: 212 2 1956 2687021 361395 0 6 TX Waiter: 224 2 913 2687021 361395 0 6 TX Waiter: 275 2 986 2687021 361395 0 6 TX Waiter: 469 2 405 2687021 361395 0 6 TX Waiter: 474 2 584 2687021 361395 0 6 TX Waiter: 485 2 1461 2687021 361395 0 6 TX Waiter: 507 2 230 2687021 361395 0 6 TX Waiter: 518 2 396 2687021 361395 0 6 TX Waiter: 525 2 399 2687021 361395 0 6 TX Waiter: 541 2 768 2687021 361395 0 6 TX Waiter: 547 2 454 2687021 361395 0 6 TX Waiter: 554 2 1043 2687021 361395 0 6 TX Waiter: 576 2 1573 2687021 361395 0 6 TX Waiter: 604 2 1395 2687021 361395 0 6 TX Waiter: 612 2 1575 2687021 361395 0 6 TX Waiter: 626 2 83 2687021 361395 0 6 TX Waiter: 631 2 125 2687021 361395 0 6 TX Waiter: 634 2 1517 2687021 361395 0 6 TX Waiter: 637 2 1747 2687021 361395 0 6 TX Waiter: 645 2 1433 2687021 361395 0 6 TX Waiter: 668 2 679 2687021 361395 0 6 TX Waiter: 700 2 1321 2687021 361395 0 6 TX Waiter: 714 2 333 2687021 361395 0 6 TX Waiter: 719 2 1108 2687021 361395 0 6 TX Waiter: 723 2 1531 2687021 361395 0 6 TX Waiter: 730 2 1549 2687021 361395 0 6 TX Waiter: 732 2 1451 2687021 361395 0 6 TX Waiter: 736 2 16 2687021 361395 0 6 TX Waiter: 739 2 1677 2687021 361395 0 6 TX Waiter: 743 2 1271 2687021 361395 0 6 TX Waiter: 710 1 353 2687021 361395 0 6 TX Waiter: 708 1 1348 2687021 361395 0 6 TX Waiter: 678 1 68 2687021 361395 0 6 TX Waiter: 604 1 654 2687021 361395 0 6 TX Waiter: 574 1 1196 2687021 361395 0 6 TX Waiter: 563 1 144 2687021 361395 0 6 TX Waiter: 236 1 1348 2687021 361395 0 6 TX Waiter: 177 1 380 2687021 361395 0 6 TX Waiter: 159 1 1152 2687021 361395 0 6 TX Waiter: 1855 1 1291 2687021 361395 0 6 TX Waiter: 946 1 219 2687021 361395 0 6 TX Waiter: 920 1 248 2687021 361395 0 6 TX Waiter: 904 1 87 2687021 361395 0 6 TX Waiter: 874 1 482 2687021 361395 0 6 TX Waiter: 873 1 232 2687021 361395 0 6 TX Waiter: 860 1 1400 2687021 361395 0 6 TX Waiter: 848 1 764 2687021 361395 0 6 TX Waiter: 844 1 862 2687021 361395 0 6 TX Waiter: 831 1 510 2687021 361395 0 6 TX Waiter: 823 1 685 2687021 361395 0 6 TX Waiter: 813 1 385 2687021 361395 0 6 TX Waiter: 810 1 1010 2687021 361395 0 6 TX Waiter: 786 1 643 2687021 361395 0 6 TX Waiter: 721 1 618 2687021 361395 0 6 TX Waiter: 748 2 1753 2687021 361395 0 6 TX Waiter: 752 2 553 2687021 361395 0 6 TX Waiter: 763 2 174 2687021 361395 0 6 TX Waiter: 769 2 1891 2687021 361395 0 6 TX Waiter: 773 2 374 2687021 361395 0 6 TX Waiter: 776 2 9 2687021 361395 0 6 TX Waiter: 777 2 0 2687021 361395 0 6 TX Waiter: 792 2 946 2687021 361395 0 6 TX Waiter: 798 2 1196 2687021 361395 0 6 TX Waiter: 809 2 1644 2687021 361395 0 6 TX Waiter: 824 2 350 2687021 361395 0 6 TX Waiter: 826 2 795 2687021 361395 0 6 TX Waiter: 830 2 1266 2687021 361395 0 6 TX Waiter: 833 2 126 2687021 361395 0 6 TX Waiter: 882 2 873 2687021 361395 0 6 TX Waiter: 885 2 1426 2687021 361395 0 6 TX sys@DB1> |
查看enqueue在等待的对象：
sys@DB1>@lock_obj INST_ID WAITER_SID OBJECT_NAME OBJECT_TYPE -------- ---------- ------------------------------ ------------------ 2 157 TABLE_77_200809 TABLE 2 518 TABLE_77_200809 TABLE 2 743 TABLE_77_200809 TABLE 2 469 TABLE_77_200810 TABLE 1 678 TABLE_77_200810 TABLE 2 485 TABLE_77_200810 TABLE 1 708 TABLE_77_200810 TABLE 2 752 TABLE_77_200810 TABLE 2 777 TABLE_77_200810 TABLE 1 904 TABLE_77_200810 TABLE 1 874 TABLE_77_200810 TABLE 1 831 TABLE_77_200810 TABLE 2 776 TABLE_77_200810 TABLE 2 736 TABLE_77_200810 TABLE 2 723 TABLE_77_200810 TABLE 2 68 TABLE_77_200811 TABLE 1 159 TABLE_77_200811 TABLE 1 177 TABLE_77_200811 TABLE 2 191 TABLE_77_200811 TABLE 2 541 TABLE_77_200811 TABLE 2 626 TABLE_77_200811 TABLE 2 612 TABLE_77_200811 TABLE 2 604 TABLE_77_200811 TABLE 1 604 TABLE_77_200811 TABLE 2 576 TABLE_77_200811 TABLE 1 574 TABLE_77_200811 TABLE 1 563 TABLE_77_200811 TABLE 2 554 TABLE_77_200811 TABLE 2 547 TABLE_77_200811 TABLE 2 773 TABLE_77_200811 TABLE 2 769 TABLE_77_200811 TABLE 2 763 TABLE_77_200811 TABLE 2 748 TABLE_77_200811 TABLE 2 739 TABLE_77_200811 TABLE 2 732 TABLE_77_200811 TABLE 2 730 TABLE_77_200811 TABLE 1 721 TABLE_77_200811 TABLE 2 719 TABLE_77_200811 TABLE 1 1855 TABLE_77_200811 TABLE 1 946 TABLE_77_200811 TABLE 1 929 TABLE_77_200811 TABLE 1 920 TABLE_77_200811 TABLE 1 911 TABLE_77_200811 TABLE 2 885 TABLE_77_200811 TABLE 2 882 TABLE_77_200811 TABLE 1 873 TABLE_77_200811 TABLE 1 860 TABLE_77_200811 TABLE 1 853 TABLE_77_200811 TABLE 1 848 TABLE_77_200811 TABLE 1 844 TABLE_77_200811 TABLE 2 833 TABLE_77_200811 TABLE 2 830 TABLE_77_200811 TABLE 2 826 TABLE_77_200811 TABLE 2 824 TABLE_77_200811 TABLE 1 823 TABLE_77_200811 TABLE 1 813 TABLE_77_200811 TABLE 1 810 TABLE_77_200811 TABLE 2 809 TABLE_77_200811 TABLE 2 798 TABLE_77_200811 TABLE 2 792 TABLE_77_200811 TABLE 1 786 TABLE_77_200811 TABLE 2 714 TABLE_77_200811 TABLE 1 710 TABLE_77_200811 TABLE 2 700 TABLE_77_200811 TABLE 2 668 TABLE_77_200811 TABLE 2 645 TABLE_77_200811 TABLE 2 637 TABLE_77_200811 TABLE 2 634 TABLE_77_200811 TABLE 2 631 TABLE_77_200811 TABLE 2 525 TABLE_77_200811 TABLE 2 507 TABLE_77_200811 TABLE 2 474 TABLE_77_200811 TABLE 2 212 TABLE_77_200811 TABLE 2 224 TABLE_77_200811 TABLE 1 236 TABLE_77_200811 TABLE 2 275 TABLE_77_200811 TABLE sys@DB1> |
查看该会话的相关信息：
sys@DB1>@get_session Enter value for input_sid: 1303 INST_ID SID USERNAME TYPE STATUS OSUSER PROCESS MACHINE PROGRAM -------- ------- --------------- ------ -------- --------------- ------------ -------------------- -------------------- 2 1303 USER1 USER INACTIVE yong.ou 3064:3068 MSHOME\A2365DC3DEB34 plsqldev.7.10.1339.e SPID INST_ID SID SERIAL# SQL_HASH_VALUE PREV_HASH_VALUE LOGON_TIME LAST_CALL_ET ------------ -------- ------- ------- --------------- ---------------- ---------------- ------------- 922300 2 1303 2684 0 1728194387 2008-12-20 09:28 1366 sys@DB1>@sql_hash Enter value for hash_value: 1728194387 SQL_TEXT ---------------------------------------------------------------- begin sys.dbms_output.get_line(line => :line, status => :statu s); end; |
通过检查，可以看出这是一个人进程，不是从应用服务器上连接过来，而且会话是”INACTIVE”状态，当前未执行其它语句，杀掉该会话后，enqueue逐渐消失：
sys@DB1>alter system kill session '1303,2684'; sys@DB1> |
— The End —
关键字: 故障案例
站内搜索