ddl锁：保护数据结构，保护对象的完整性，也叫字典锁。
当我们想要向表中增加一列，要求我们先要锁定表的结构，然后增加一个新的列。
select table_name,table_lock from user_tables;
table_name table_lo ------------------------------ -------- dept enabled emp enabled bonus enabled salgrade enabled elapsed: 00:00:00.11
创建一张临时表
create table e01 as select * from emp;
select table_name,table_lock from user_tables;
table_name table_lo ------------------------------ -------- dept enabled emp enabled bonus enabled salgrade enabled e01 enabled elapsed: 00:00:00.02
alter table e01 disable table lock;
select table_name,table_lock from user_tables;
table_name table_lo ------------------------------ -------- dept enabled emp enabled bonus enabled salgrade enabled e01 disabled elapsed: 00:00:00.03
现在表锁处于disable状态
下面我们对表做一些操作
truncate table e01;
truncate table e01
*
error at line 1:
ora-00069: cannot acquire lock -- table locks disabled for e01
elapsed: 00:00:00.03
drop table e01;
drop table e01
*
error at line 1:
ora-00069: cannot acquire lock -- table locks disabled for e01
elapsed: 00:00:00.12
我们把锁打开
alter table e01 enable table lock;
select table_name,table_lock from user_tables;
table_name table_lo ------------------------------ -------- dept enabled emp enabled bonus enabled salgrade enabled e01 enabled elapsed: 00:00:00.02
truncate table e01;
table truncated. elapsed: 00:00:00.09
dml锁：在事务中产生的，为了保证并发数据一致性的锁，存在于行头，叫做行级锁
用sys用户授予scott用户查看视图权限
grant select on v_$mystat to scott;
查看当前会话的sid
select sid from v$mystat where rownum=1;
sid
----------
38
elapsed: 00:00:00.00
在sys用户下查看该会话有哪些锁
select * from v$lock where sid=38;
addr kaddr sid ty id1 id2 lmode request ctime block ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 000000009705a0a8 000000009705a100 38 ae 100 0 4 0 1822 0 000000009705a248 000000009705a2a0 38 to 65927 1 3 0 425 0 elapsed: 00:00:00.03
scott用户模拟会话产生锁
insert into e01 select * from emp;
sys用户查看锁的状态
select * from v$lock where sid=38;
addr kaddr sid ty id1 id2 lmode request ctime block ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 000000009705a0a8 000000009705a100 38 ae 100 0 4 0 2014 0 000000009705a248 000000009705a2a0 38 to 65927 1 3 0 617 0 00007faec3237828 00007faec3237888 38 tm 74754 0 3 0 52 0 0000000096383280 00000000963832f8 38 tx 786441 6 6 0 48 0 elapsed: 00:00:00.00
我们队scott用户事务进行提交
commit;
再查看锁的状态
select * from v$lock where sid=38;
addr kaddr sid ty id1 id2 lmode request ctime block ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 000000009705a0a8 000000009705a100 38 ae 100 0 4 0 2112 0 000000009705a248 000000009705a2a0 38 to 65927 1 3 0 715 0 elapsed: 00:00:00.00
oracle是自动管理锁的资源的，在不同时间对不同对象操作就会产生不同的锁，如果两个会话同时修改一张表的同一行信息，会出现锁的征用问题，他们会以队列的形式进行排队处理
我们开启两个scott用户的会话同时执行一个sql
update e01 set sal=sal+1 where empno=7369;
我们用sys用户来看下锁队列的情况
@?/rdbms/admin/utllockt
drop table lock_holders
*
error at line 1:
ora-00942: table or view does not exist
elapsed: 00:00:00.00
table created.
elapsed: 00:00:00.02
drop table dba_locks_temp
*
error at line 1:
ora-00942: table or view does not exist
elapsed: 00:00:00.00
table created.
elapsed: 00:00:00.03
1 row created.
elapsed: 00:00:00.00
commit complete.
elapsed: 00:00:00.01
table dropped.
elapsed: 00:00:00.03
1 row created.
elapsed: 00:00:00.00
commit complete.
elapsed: 00:00:00.00
waiting_session lock_type mode_requested mode_held lock_id1 lock_id2
----------------- ----------------- -------------- -------------- ----------------- -----------------
36 none
38 transaction exclusive exclusive 1310747 6
elapsed: 00:00:00.00
table dropped.
elapsed: 00:00:00.03
如果出现两个会话交叉入队，都在等待另一方把锁放开，oracle会中断其中的一个会话，这种情况叫死锁。
我们手工将处于资源等待的会话杀掉
select sid,serial# from v$session where sid=38;
sid serial#
---------- ----------
38 6
elapsed: 00:00:00.00
alter system kill session '38,6' immediate;