糟糕！核心业务锁表,抓瞎了？

作者：IT邦德
中国DBA联盟(ACDU)成员，10余年DBA工作经验，
Oracle、PostgreSQL ACE
CSDN博客专家及B站知名UP主，全网粉丝10万+
擅长主流Oracle、MySQL、PG、
高斯及Greenplum备份恢复，
安装迁移，性能优化、故障应急处理

微信:jem_db
QQ交流群:587159446
公众号：IT邦德

@

• 1.锁表产生的场景

• 2.Oracle案例

• 2.1 排查锁的发生

• 2.2 Oracle解锁

• 3.MySQL案例

• 3.1 查看当前锁表事务

• 3.2 锁的释放

• 4.总结

前言

近期系统在使用过程中突发了几次数据库锁表情况，做个总结，避免以后在出现，大家忙手忙脚的

1.锁表产生的场景

数据库锁表通常发生在以下几种场景中：
1.DML操作：在执行INSERT、UPDATE、DELETE等数据操纵语言（DML）操作时，
如果涉及的数据行已经被其他事务锁定，
当前操作可能会等待锁释放，从而导致锁表

2.DDL操作：使用ALTER TABLE、TRUNCATE TABLE
等数据定义语言（DDL）语句对表进行结构性修改时，
需要锁定整个表以防止其他会话对表进行操作，这可能导致锁表

3.索引缺失或不当使用：如果UPDATE语句或DELETE语句的WHERE条件没有走索引，
或者查询时使用的索引不可用，
可能会导致锁表，因为数据库可能会选择使用表锁而不是行锁。 

4.长事务或大事务：当一个事务长时间占用锁资源或操作大量数据时，
可能会导致其他事务等待锁释放，从而形成锁表。 

5.死锁：当多个事务相互等待对方释放锁时，
可能会产生死锁，导致锁表问题。 


6.并发操作：在高并发场景下，
如果多个事务同时尝试修改同一资源，可能会导致锁表。 

2.Oracle案例

2.1 排查锁的发生

--通过以下的SQL找出阻塞的seesion

select * from dba_hist_active_sess_history a
where a.sample_time between
to_date('202008011230',  'yyyymmddhh24mi')
and to_date('202012121230', 'yyyymmddhh24mi')；

BLOCKING_SESSION：阻塞会话的ID
CURRENT_OBJ#：会话当前引用的对象的对象ID

使用DBA_HIST_ACTIVE_SESS_HISTORY可以帮助DBA跟踪和调试数据库性能问题，
了解会话的行为和影响，在排查行锁中非常实用。

定位SQL

select CPU_COST,IO_COST,COST from v$sql_plan 
where sql_id='6qd38pjx143g0'; 

select sql_id,sql_text from v$sql where sql_text 
where sql_id='6qd38pjx143g0'; 

定位被锁的对象
select * from dba_objects where object_id=4333

2.2 Oracle解锁

SELECT SESS.SID,  
SESS.SERIAL#,  
LO.ORACLE_USERNAME,  
LO.OS_USER_NAME,  
AO.OBJECT_NAME 被锁对象名, 
LO.LOCKED_MODE 锁模式, 
sess.LOGON_TIME 登录数据库时间,
'ALTER SYSTEM KILL SESSION ''' 
|| SESS.SID || ','||SESS.SERIAL#||'''' FREESQL
FROM V$LOCKED_OBJECT LO,  DBA_OBJECTS AO,  V$SESSION SESS 
WHERE AO.OBJECT_ID = LO.OBJECT_ID 
AND LO.SESSION_ID = SESS.SID 
ORDER BY sid, sess.serial#;

通过此sql可以查询到以下内容：

复制完执行可能会报错：ORA-00031: session marked for kill，这表示ORACLE已经把它标记为一个杀死的进程，但暂时无法将其彻底杀死，这个时候需要我们执行下面的sql，查出它在服务器上的进程id：

# sid 为上面sql 查出来的 sid
select spid, osuser, s.program
   from v$session s,v$process p
   where s.paddr=p.addr
   and s.sid='24986' 

通过上方 sql 可以得到服务器上的进程 id，登录数据库所在服务器，利用 kill 命令将其杀死即可：

kill -9 12009（查出来的spid）

3.MySQL案例

3.1 查看当前锁表事务

查看是否锁表SQL语句
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SHOW OPEN TABLES WHERE `Table` = 'table_name' 
AND `Database` = 'database_name';

3.2 锁的释放

MySQL中锁的释放是自动进行的，当一个会话执行完相关操作后，所持有的锁会自动释放。不过，有些情况下我们可能需要手动释放锁，比如长事务或者死锁的处理。

4.总结

为了避免锁表，可以采取优化SQL语句、合理使用索引、减少事务的持有时间、使用事务隔离级别、分离读写操作等措施。在生产环境中，还应该实施监控和警报机制，以便及时发现并处理锁表问题。