【应用终止后，数据库中的长事务何去何从？】

原创张sir 2022-11-14

334

前言

在日常的运维过程中，经常碰到由于慢SQL导致数据库堵塞的情况，如果收集了统计信息或者加了索引以后，新执行的SQL没问题了，那还在跑的SQL怎么办？这个时候一般我们会选择重启应用或者重启数据库或者把会话杀掉。有的公司应用和数据库是分不同团队或者部门运维的，在应急的时候，到底是重启应用？重启数据库？杀会话？可能还得扯皮一会儿。这三种方法中，重启数据库是最彻底的，所有没执行完的事务回滚，杀会话会让指定的事务回滚，但是某些时候会有回滚不掉的情况。那么重启应用呢？这里面我觉得分成两个场景：1、应用程序直接被kill。 2、应用程序是正常停止的。这里先测试下第一种场景，我不太会写应用程序，第二种还在研究ing。

一、编写测试程序

写了一段java小代码，可以用来连接oracle，执行增删查改，这段demon就是利用test用户连接数据库，把表student的name字段更新成'aaaa'。

[root@ops-dev-app software]# cat ConnOracle.java 
import java.sql.*;
public class ConnOracle {
    public static void main(String[] args) throws SQLException {
        OracleJdbcTest test = new OracleJdbcTest();
        try {
            test.query("select count(*) from student");
        } catch (SQLException e) {}
        test.query("update student set name='aaaa'");
        test.query("select sysdate from dual");
        test.close();
    }
}
[root@ops-dev-app software]# cat OracleJdbcTest.java 
import java.sql.*;
public class OracleJdbcTest {
    private static Connection conn = null;
    private static String driver = "oracle.jdbc.driver.OracleDriver";
    private static String url = "jdbc:oracle:thin:@//10.50.226.18:1521/orcl11g";
    private static String username = "test";
    private static String password = "test";
    private static synchronized Connection getConn(){
        if(conn == null){
            try {
                Class.forName(driver);
                conn = DriverManager.getConnection(url, username, password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return conn;
    }
    public void query(String sql, boolean isSelect) throws SQLException{
        PreparedStatement pstmt;
         
        try {
            pstmt = getConn().prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                String name = rs.getString("name");
                System.out.println(name);
            }
            rs.close();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
     
    public void query(String sql) throws SQLException{
        PreparedStatement pstmt;
        pstmt = getConn().prepareStatement(sql);
        pstmt.execute();
        pstmt.close();
    }
    public void close(){
        try {
            getConn().close();
             
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
     
}复制

二、环境配置

有了java代码，还需要对环境做下配置：

1、从数据库的安装目录下找到ojdbc驱动包

[root@ops-dev-app software]# cd /u01/app/oracle/product/11.2.0/jdbc/lib/
[root@ops-dev-app lib]# ls
ojdbc5dms_g.jar  ojdbc5dms.jar  ojdbc5_g.jar  ojdbc5.jar  ojdbc6dms_g.jar  ojdbc6dms.jar  ojdbc6_g.jar  ojdbc6.jar  simplefan.jar
复制

2、拷贝到操作系统的/usr/java/jdk1.7.0_80/jre/lib/ext目录下，光这个路径我找了半天。

3、配置环境变量classpath，其中software是我上面两个java文件的路径。

[root@ops-dev-app lib]# cat ~/.bash_profile 
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
	. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
CLASSPATH=/usr/java/jdk1.7.0_80/jre/lib/
CLASSPATH=$CLASSPATH:/software
PATH=$PATH:$CLASSPATH export PATH复制

三、编译并执行

编译成功会生成两个.class文件。执行就直接运行java ConnOracle

[root@ops-dev-app software]# javac ConnOracle.java
[root@ops-dev-app software]# ls
ConnOracle.class  jboss-as-7.1.0.Final.zip  mysql-8.0.24-el7-x86_64.tar.gz  OracleJdbcTest.class  proxysql-2.4.4-1-centos7.x86_64.rpm
ConnOracle.java   jdk-7u80-linux-x64.rpm    ojdbc6.jar                      OracleJdbcTest.java复制

四、建测试表

在oracle中建一个测试大表student，这样通过update提供一个大事务，给我们足够的时间去测试。

SQL> conn test/test
Connected.
SQL> desc student;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID						    NUMBER(38)
 NAME						    NCHAR(20)

SQL> update student set name='zzz';

8388608 rows updated.

SQL> commit;

Commit complete.

SQL> select * from student where rownum<10;

	ID NAME
---------- --------------------
	 1 zzz
	 2 zzz
	 1 zzz
	 2 zzz
	 1 zzz
	 2 zzz
	 1 zzz
	 2 zzz
	 1 zzz

9 rows selected.

SQL> select count(*) from student;   

  COUNT(*)
----------
   8388608复制

五、测试

1、运行java程序
java ConnOracle
2、登录数据库查看当前执行的SQL情况，可以看到有一个update在执行。
SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss';

Session altered.

Elapsed: 00:00:00.01
SQL> set line 200 pages 1000
SQL> col event for a30
SQL> col program for a25
SQL> col username for a10
SQL> col sql_id for a15
SQL> col machine for a16
SQL> col ssid for a13
SQL> col state for a20
SQL> col status for a10
SQL> select sysdate from dual;

SYSDATE
-----------------
20221114 11:20:53

Elapsed: 00:00:00.02
SQL> select inst_id||':'||sid||','||serial# ssid,username,sql_id,event,substr(program,1,25) program,machine,state,last_call_et exec_time,status,blocking_session from gv$session
  2  where wait_class<>'Idle' and username is not null
  3  order by last_call_et;

SSID	      USERNAME	 SQL_ID 	 EVENT				PROGRAM 		  MACHINE	   STATE		 EXEC_TIME STATUS     BLOCKING_SESSION
------------- ---------- --------------- ------------------------------ ------------------------- ---------------- -------------------- ---------- ---------- ----------------
1:19,97       TEST	 9649bccs7yhun	 SQL*Net message to client	sqlplus@ops-dev-app (TNS  ops-dev-app	   WAITED SHORT TIME		 0 ACTIVE
1:141,9009    TEST	 b5wzfy7zm289m	 db file scattered read 	JDBC Thin Client	  ops-dev-app	   WAITED SHORT TIME		29 ACTIVE

Elapsed: 00:00:00.03
SQL> 
SQL> 
SQL> select sysdate from dual;

SYSDATE
-----------------
20221114 11:20:55

Elapsed: 00:00:00.00
SQL> select sql_text from v$sql where sql_id='b5wzfy7zm289m';

SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
update student set name='aaaa'

Elapsed: 00:00:00.033、把java执行程序ctrl+c掉。4、再次查看SQL执行情况，可以看到这个update还在执行。SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss';

Session altered.

Elapsed: 00:00:00.00
SQL> set line 200 pages 1000
SQL> col event for a30
SQL> col program for a25
SQL> col username for a10
SQL> col sql_id for a15
SQL> col machine for a16
SQL> col ssid for a13
SQL> col state for a20
SQL> col status for a10
SQL> select sysdate from dual;

SYSDATE
-----------------
20221114 11:21:39

Elapsed: 00:00:00.00
SQL> select inst_id||':'||sid||','||serial# ssid,username,sql_id,event,substr(program,1,25) program,machine,state,last_call_et exec_time,status,blocking_session from gv$session
  2  where wait_class<>'Idle' and username is not null
  3  order by last_call_et;

SSID	      USERNAME	 SQL_ID 	 EVENT				PROGRAM 		  MACHINE	   STATE		 EXEC_TIME STATUS     BLOCKING_SESSION
------------- ---------- --------------- ------------------------------ ------------------------- ---------------- -------------------- ---------- ---------- ----------------
1:19,97       TEST	 9649bccs7yhun	 SQL*Net message to client	sqlplus@ops-dev-app (TNS  ops-dev-app	   WAITED SHORT TIME		 0 ACTIVE
1:141,9009    TEST	 b5wzfy7zm289m	 log file switch (checkpoint in JDBC Thin Client	  ops-dev-app	   WAITING			75 ACTIVE		   130
					 complete)


Elapsed: 00:00:00.00
SQL> 
SQL> 
SQL> select sysdate from dual;

SYSDATE
-----------------
20221114 11:21:41

Elapsed: 00:00:00.00
SQL> select sql_text from v$sql where sql_id='b5wzfy7zm289m';

SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
update student set name='aaaa'
Elapsed: 00:00:00.00
5、等SQL执行完以后，查看表student已经全部被更新成了'aaaa'。
SQL> 
SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss';

Session altered.

Elapsed: 00:00:00.00
SQL> set line 200 pages 1000
SQL> col event for a30
SQL> col program for a25
SQL> col username for a10
SQL> col sql_id for a15
SQL> col machine for a16
SQL> col ssid for a13
SQL> col state for a20
SQL> col status for a10
SQL> select sysdate from dual;

SYSDATE
-----------------
20221114 11:22:02

Elapsed: 00:00:00.00
SQL> select inst_id||':'||sid||','||serial# ssid,username,sql_id,event,substr(program,1,25) program,machine,state,last_call_et exec_time,status,blocking_session from gv$session
  2  where wait_class<>'Idle' and username is not null
  3  order by last_call_et;

SSID	      USERNAME	 SQL_ID 	 EVENT				PROGRAM 		  MACHINE	   STATE		 EXEC_TIME STATUS     BLOCKING_SESSION
------------- ---------- --------------- ------------------------------ ------------------------- ---------------- -------------------- ---------- ---------- ----------------
1:19,97       TEST	 9649bccs7yhun	 SQL*Net message to client	sqlplus@ops-dev-app (TNS  ops-dev-app	   WAITED SHORT TIME		 0 ACTIVE

Elapsed: 00:00:00.00
SQL> 
SQL> 
SQL> select sysdate from dual;

SYSDATE
-----------------
20221114 11:22:04

Elapsed: 00:00:00.00

SQL> select * from student where rownum<10;

	ID NAME
---------- --------------------
	 1 aaaa
	 2 aaaa
	 1 aaaa
	 2 aaaa
	 1 aaaa
	 2 aaaa
	 1 aaaa
	 2 aaaa
	 1 aaaa

9 rows selected.复制