当另外一个进程已经为状态数据库加锁了怎么解决
发布网友
发布时间：2022-05-02 15:05
共2个回答
懂视网
时间：2022-05-02 19:26
我们在更新数据库表结构的时候，当数据库有连接正在进行中的事务时，那么你的更新请求会处于一个等待的状态，一直等待到当前未提交的事务完成之后才会进行更新操作，但是这个未提交的事务会需要多久时间完成对我们来讲是一个未知数，（自己第一次碰到这个情况的时候，竟然以为是数据记录太多需要等待更新，等了半个小时... 愚蠢）在这个等待期间，所有的后续连接请求都会被挂起，等待事务提交完成后更新操作完成才会执行，逻辑是这样的：有个事务1在查询表a，一直占着不释放，那么这个时候执行ddl操作，就会被事务1阻塞，直到事务1被提交或者被kill。如果当你在更新表结构时碰到了这种情况，不要等，等的越久你会发现自己越蠢。
首先说一下我们在执行ddl操作前需要注意的几点：
第一点：
不要使用navicat进行该操作，在你使用sql语句时，卡死掉我们ctrl+c结束正在执行的语句就好了，但是当我们利用navicat这种第三方工具连接时，一旦语句卡死，navicat这个程序也会无响应，并且！你将程序进程结束掉仍然无法停止正在执行的语句！（大坑！）
第二点：
在更新前要挑选一个业务的低峰期进行更新，这个是在询问阿里怎样避免因为更新时出现此机制影响线上环境时阿里告诉我的办法，emmm...
接下来说一下碰到这种问题时我们应该怎么去解决：
第一点：
我们在执行ddl操作前可以执行一下 select * from information_schema.innodb_trx 来看一下我们当前有没有未提交的事务，可根据结果中的trx_state来看当前线程的状态
第二点：
在上条语句的查询结果中我们可以找到有一个trx_mysql_thread_id列，这个列名对应的值我们可以在show processlist中找到对应的记录，找到后可以查看对应记录的host列以及对应的用户和库，说明该主机还有连接没有断开
第三点：
在找到这些信息之后我们判断该条连接是否可以kill，如果可以，我们可以 kill id(替换为对应连接的trx_mysql_thread_id) 之后再进行ddl操作
这时没有了未提交需要等待的事务，我们的表结构更新语句便会顺利执行成功。
关于更新表结构时碰到的DDL锁导致数据库无法连接
标签：无法连接 ... state 无响应 语句 用户 第三方 业务 使用
热心网友
时间：2022-05-02 16:34
加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。
在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。
根据保护的对象不同，Oracle数据库锁可以分为以下几大类：
DML锁（data locks，数据锁），用于保护数据的完整性；
DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；
内部锁和闩（internal locks and latches），保护数据库的内部结构，应用于SGA；
在我们实际应用开发中涉及较多的是DML锁，其他两种的话DBA会更加关心点；
DML锁的目的在于保证并发情况下的数据完整性，主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。
当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、X等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。如下图所示：
值 锁模式 锁描述 SQL
0 NONE
1 NULL 空 SELECT
2 SS(ROW-S) 行级共享锁
其他对象只能查询这些数据行 SELECT FOR UPDATE、LOCK FOR UPDATE、
LOCK ROW SHARE
3 SX(ROW-X) 行级排它锁
在提交前不允许做DML操作 INSERT、UPDATE、DELETE、
LOCK ROW SHARE
4 S(SHARE) 共享锁 CREATE INDEX、LOCK SHARE
5 SSX(S/ROW-X) 共享行级排它锁 LOCK SHARE ROW EXCLUSIVE
6 X(eXclusive) 排它锁 ALTER TABLE、DROP TABLE、DROP INDEX、
TRUNCATE TABLE、LOCK EXCLUSIVE
在数据行上只有X锁（排他锁）。在 Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行 DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。
在大概了解oracle的锁机制之后，我们来解决几个基本的问题：
1.UPDATE/DELETE操作会将RS锁定，直至操作被COMMIT或者ROLLBACK；
若操作未COMMIT之前其他session对同样的RS做变更操作，则操作会被hold，直至前session的UPDATE/DELETE操作被COMMIT；
2.session内外SELECT的RS范围
前提：INSERT、UPDATE操作未COMMIT之前进行SELECT；
若在同一session内，SELECT出来的RS会包括之前INSERT、UPDATE影响的记录；
若不在同一session内，SELECT出来的RS不会包括未被COMMIT的记录；
3.SELECT.... FOR UPDATE [OF cols] [NOWAIT/WAIT] [SKIP LOCKED]
OF cols：只锁定指定字段所在表的RS，而没有指定的表则不会锁定，只会在多表联合查询时出现；
NOWAIT：语句不会hold，而是直接返回错误ORA-00054: resource busy and acquire with NOWAIT specified；
WAIT N：语句被hold N秒，之后返回错误ORA-30006: resource busy; acquire with WAIT timeout expired；
SKIP LOCKED：不提示错误，而是直接返回no rows selected；
以上几个选项可以联合使用的，比较推荐的有：
SELECT.... FOR UPDATE NOWAIT：对同一RS执行该SQL时，直接返回错误；
SELECT.... FOR UPDATE NOWAIT SKIP LOCKED：对同一RS执行该SQL时，直接返回空行；
PS：当RS被LOCK住之后，只对同样请求LOCK的语句有效，对无需LOCK的SELECT语句并没有任何影响；
