数据库锁问题排查

Oracle：

awr（sql执行等信息）->alert（报错信息）->trance日志

1、查看哪些会话锁了哪些表
select l.session_id,o.owner,o.object_name
from v$locked_object l,dba_objects o
where l.object_id=o.object_id;

2、查询锁表的会话信息
select s.username,s.sid,s.serial#,s.logon_time
from  v$locked_object l,v$session s
where l.session_id=s.sid 
order by s.logon_time;

3、根据查询出的sid和serial#查询会话信息
select * from v$session where sid=48 and serial#=387;

4、根据3中查询到的sql_id信息查询该会话执行的是什么SQL导致锁
select sql_fulltext from v$sql where sql_id='5updgzg8hrrua';

 

---------------method 2---------------------

1.测试相关语句：
sqlplus sys/sys@10.7.20.157:1522/dcits as sysdba

DROP TABLE AC_BRANCH CASCADE CONSTRAINTS;
CREATE TABLE AC_BRANCH
(
  BRANCH              VARCHAR2(20 CHAR) primary key NOT NULL,
  SETTLE_LEVEL        VARCHAR2(20 CHAR) NOT NULL,
  SETTLE_BRANCH       VARCHAR2(20 CHAR),
  SETTLE_SUBJECT      VARCHAR2(30 CHAR),
  SETTLE_ACCT_SEQ     VARCHAR2(20 CHAR),
  SETTLE_SUBJECT_UP   VARCHAR2(30 CHAR),
  SETTLE_ACCT_SEQ_UP  VARCHAR2(20 CHAR)
)

SET AUTOCOMMIT OFF;
show autocommit;

select * from ac_branch where BRANCH='1';
select * from ac_branch where BRANCH='1' for UPDATE;
INSERT INTO ac_branch VALUES('1', '2', '3', '4', '5', '6', '7');

2.锁查询sql：
select * from V$SESSION where type='USER' and username='TEST';      --会话的详细信息
select * FROM v$process;    --显示Oracle所有进程的信息（包括后台进程和服务器进程）
SELECT * FROM v$lock;   --用于显示锁的信息，通过与V$SESSION进行连接查询，可以显示占有锁的会话，以及等待锁的会话
    select a.username,a.machine,b.lmode,b.request from v$session a,v$lock b
            where a.sid=b.sid and a.type='USER'; 
SELECT * FROM v$locked_object; --显示被加锁的数据库对象，通过与DBA_OBJECT进行连接查询，
                            --可以显示具体的对象名及执行加锁操作的ORACLE用户名
                            --(dba_为dba用户拥有或可以访问的对象，all_为当前用户拥有或可以访问的对象，user_为当前用户拥有的对象)
    select a.oracle_username,b.owner||'.'||b.object_name object from V$LOCKED_OBJECT a,dba_objects b   
            where a.object_id = b.object_id;
SELECT * FROM v$session_wait;   --查询用户当前的等待信息. 以查看当前的语句为什么这么慢/在等待什么资源.
SELECT * FROM v$sqlarea;    --根据sql_text进行group by的结果，v$sql中为每一条SQL保留一个条目
SELECT * FROM dba_objects;

排查问题:
1. 查询出被锁对象(表)被哪几个会话占用
    select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

2. 查询出会话的相关信息(会话唯一标识sid+serial#,锁等待的sql_id,sql开始执行时间sql_exec_id;持有锁会话的上一执行pre_sql_id,持有锁会话上一执行sql的执行时间pre_exec_start)
    select b.username,b.sid,b.serial#,b.logon_time,b.sql_id,b.sql_exec_start,b.prev_sql_id,b.prev_exec_start,b.module from v$locked_object a,v$session b where a.session_id = b.sid ;

3. 查询锁等待(或持有锁会话的上一执行sql)的sql信息：
    SELECT SQL_ID,SQL_TEXT,sql_fulltext,LAST_ACTIVE_TIME,MODULE,ACTION,parsing_schema_name FROM V$SQL where sql_id = '89k3dqjqjr37p';

4. 查询指定时间执行的sql：
    SELECT SQL_ID,SQL_TEXT,sql_fulltext,LAST_ACTIVE_TIME,MODULE,ACTION,parsing_schema_name FROM V$SQL WHERE LAST_ACTIVE_TIME=TO_DATE('2019-11-05 15:05:33','YYYY-MM-DD HH24:MI:SS');

5. 确认若是认为原因导致的锁，则kill掉会话：
    alter system kill session'525,18065';

 

 

 

mysql:

 

 

1.查询设置事务隔离级别(不同的事务隔离级别可能解决脏读、不可重复读、泛读的问题)
select @@tx_isolation;
set session TRANSACTION ISOLATION LEVEL Serializable;        --Read uncommitted | Read committed | Repeatable | Serializable

2.查询事务、锁、会话(mysql进程列表)的sql：
    information_schema.PROCESSLIST    当前会话(进程)列表
    information_schema.INNODB_TRX    当前正在执行的事务(含sql已经执行完成，但事务没有提交的事务，事务提交后，事务结束)
    information_schema.INNODB_LOCK_WAITS    锁等待和持有锁的关系
    information_schema.INNODB_LOCKS        锁的相关信息(所等待的原因)

-- 查询当前的会话 有 哪些有事务正在执行
select a.*,'分隔' as 分隔,b.* from 
        information_schema.PROCESSLIST a        -- 连接mysql的会话(进程列表)
        left JOIN
        information_schema.INNODB_TRX b        -- 正在执行的事务（事务从开始到commit,记录会一直存在，sql语句执行完则不会显示正在执行的sql。查询也会启事务）
        on a.ID = b.trx_mysql_thread_id;

--  锁等待和持有锁的相互关系(request为需要锁的事务id和锁等待id，block为加锁的事务id和持有锁id)
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 锁等待的原因
SELECT * FROM information_schema.INNODB_LOCKS;

3.kill持有锁的会话：
    kill + id

4.测试相关语句：
    mysql -h 10.7.20.160 -P 3306 -u root -proot
    show VARIABLES like '%autocommit%';
    set autocommit=off;
    select * from ac_branch where BRANCH='1';
    select * from ac_branch where BRANCH='1' for UPDATE;
    INSERT INTO ac_branch VALUE ('10', '2', '3', '4', '5', '6', '7');
    commit;