数据库锁表与解锁
数据库锁表与解锁
一、mysql
锁定表:LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]
解锁表:UNLOCK TABLES
例子:
LOCK TABLES table1 WRITE ,table2 READ 、、、更多表枷锁;
说明:1、READ 锁代表其她用户只能读不能其她操作
2、WRITE锁代表:其她用户不能任何操作(包括读)
查瞧那些表被锁:show OPEN TABLES where In_use > 0;
全局加锁:FLUSH TABLES WITH READ LOCK(这个命令就是全局读锁定,执行了命令之后所有库所有表都被锁定只读。解锁也就是:UNLOCK TABLES )
二、oracle
--行级锁定(同样对 mysql起作用)
通过 :select * from tableName t for update 或 select * from tableName
t where id =1 for update
前者锁定整个表,后者多顶 id=1的一行数据(有主键,并且指定主键=值的只
锁定指定行)
说明:通过 select 、、、 for update 后其她用户只能读不能其她操作,锁定者通过 commit或 rollback命令自动解锁,或使用本文的解锁方式
(will)！
--表级锁定
lock table
其中:
lock_mode 就是锁定模式
nowait关键字用于防止无限期的等待其她用户释放锁
五种模式如下(1到5 级别越来越高,限制越来越大):
1、行共享(row share,rs):允许其她用户访问与锁定该表,但就是禁止排她锁定
整个表
2、排她锁(row exclusive ,rx):与行共享模式相同,同时禁止其她用户在此表上使用共享锁。使用select 、、、 for update语句会在表上自动应用行排她锁
3、共享(share ,s):共享锁将锁定表,仅允许其她用户查询表中的行,但不允许插入、更新、删除行。多个用户可以在同一表中放置共享锁,即允许资源共享,,因此得名“共享锁”。例如:如果用户每天都需要在结账时更新日销售额表,则可以在更新该表时使用共享锁以确保数据的一致性。
4、共享排她锁(share row exclusive,srx):执行比共享锁更多的限制。防止其
她事务在表上应用共享锁,、共享排她锁以及排她锁。
5、排她(exclusive,x):对表执行最大的限制。除了允许其她用户查询该表记录,
排她锁防止其她事务对表做任何更改或在表上应用任何类型的锁。
实例:
lock table table_Name in exclusive mode;
要解锁需要锁定人执行 commit 或 rollback 或者用本文的解锁方式
(will)！
--查询锁表
SELECT /*+ rule */
S、USERNAME,
DECODE(L、TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,
O、OWNER,
O、OBJECT_NAME,
O、OBJECT_TYPE,
S、SID,
S、SERIAL#,
S、TERMINAL,
S、MACHINE,
S、PROGRAM,
S、OSUSER
FROM V$SESSION S, V$LOCK L, DBA_OBJECTS O
WHERE L、SID = S、SID
AND L、ID1 = O、OBJECT_ID(+)
AND S、USERNAME IS NOT NULL;
--查询状态
SELECT SESSION_ID SID,
OWNER,
NAME,
TYPE,
MODE_HELD HELD,
MODE_REQUESTED REQUEST
FROM DBA_DDL_LOCKS
WHERE NAME = 'DRAG_DATA_FROM_LCAM';
SELECT T1、SID, T1、SERIAL#, T2、SQL_TEXT
FROM V$SESSION T1, V$SQL T2
WHERE T1、SQL_ID = T2、SQL_ID
AND T2、SQL_TEXT LIKE '%DRAG_DATA_FROM_LCAM%';
SELECT DISTINCT P、SPID, S、SID, S、SERIAL# FROM
V$DB_OBJECT_CACHE OC,
V$OBJECT_DEPENDENCY OD,
DBA_KGLLOCK W,
V$SESSION S,
V$PROCESS P WHERE OD、TO_OWNER = OC、OWNER
AND OD、TO_NAME = OC、NAME
AND OD、TO_ADDRESS = W、KGLLKHDL
AND W、KGLLKUSE = S、SADDR
AND P、ADDR = S、PADDR
AND OC、NAME = UPPER('drag_data_from_lcam');
Oracle的锁表与解锁
SELECT /*+ rule */ s、username,
decode(l、type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o、owner,o、object_name,o、object_type,
s、sid,s、serial#,s、terminal,s、machine,s、program,s、osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l、sid = s、sid
AND l、id1 = o、object_id(+)
AND s、username is NOT Null
--kill session语句 (说明 :下面的 50就是查询结果中sid字段值,492就是
serial#字段值)
alter system kill session'50,492'; (需要dba权限)
--以下几个为相关表
SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session;
SELECT * FROM v$process ;
SELECT * FROM v$locked_object;
SELECT * FROM all_objects;
SELECT * FROM v$session_wait;
--1、查出锁定object的session的信息以及被锁定的object名SELECT l、session_id sid, s、serial#, l、locked_mode,l、oracle_username, l、os_user_name,s、machine, s、terminal, o、object_name, s、logon_time FROM v$locked_object l, all_objects o, v$session s
WHERE l、object_id = o、object_id
AND l、session_id = s、sid
ORDER BY sid, s、serial# ;
--2、查出锁定表的session的sid, serial#,os_user_name, machine name,
terminal与执行的语句
--比上面那段多出sql_text与action
SELECT l、session_id sid, s、serial#, l、locked_mode, l、oracle_username,
s、user#,
l、os_user_name,s、machine, s、terminal,a、sql_text, a、action FROM v$sqlarea a,v$session s, v$locked_object l
WHERE l、session_id = s、sid
AND s、prev_sql_addr = a、address
ORDER BY sid, s、serial#;
--3、查出锁定表的sid, serial#,os_user_name, machine_name, terminal,锁
的type,mode
SELECT s、sid, s、serial#, s、username, s、schemaname, s、osuser, s、
process, s、machine,
s、terminal, s、logon_time, l、type
FROM v$session s, v$lock l
WHERE s、sid = l、sid
AND s、username IS NOT NULL
ORDER BY sid;
这个语句将查找到数据库中所有的DML语句产生的锁,还可以发现,
任何DML语句其实产生了两个锁,一个就是表锁,一个就是行锁。
杀锁命令
alter system kill session 'sid,serial#'
SELECT /*+ rule */ s、username,
decode(l、type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o、owner,o、object_name,o、object_type,
s、sid,s、serial#,s、terminal,s、machine,s、program,s、osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l、sid = s、sid
AND l、id1 = o、object_id(+)
AND s、username is NOT NULL
如果发生了锁等待,我们可能更想知道就是谁锁了表而引起谁的等待
以下的语句可以查询到谁锁了表,而谁在等待。
以上查询结果就是一个树状结构,如果有子节点,则表示有等待发生。
如果想知道锁用了哪个回滚段,还可以关联到V$rollname,其中xidusn就就是回
滚段的USN
col user_name format a10
col owner format a10
col object_name format a10
col object_type format a10
SELECT /*+ rule */ lpad(' ',decode(l、xidusn ,0,3,0))||l、oracle_username
User_name,
o、owner,o、object_name,o、object_type,s、sid,s、serial# FROM v$locked_object l,dba_objects o,v$session s
WHERE l、object_id=o、object_id
AND l、session_id=s、sid
ORDER BY o、object_id,xidusn DESC