课程第17次

botang

1. select  sq.SQL_FULLTEXT from v_$sql sq where sq.SQL_ID=(
   
2. select  s.SQL_ID from v_$session s where s.BLOCKING_SESSION is not null);
   
3. 
   
4. select sid from v$session
   
5. where sid in (select  blocking_session from v$session
   
6.   where blocking_session is not null);
   
7. 
   
8. 
   
9. select  * from v_$lock  k
   
10. where k.SID in (37, 136 )
    
11. order by sid ;

复制代码

	ADDR	KADDR	SID	TYPE	ID1	ID2	LMODE	REQUEST	CTIME	BLOCK
1	00000000BBD411E0	00000000BBD41238	37	TX	393216	1079	0	6	2546	0
2	00007FF2363EEF90	00007FF2363EEFF0	37	TM	87361	0	3	0	2570	0
3	00000000BBD41FF8	00000000BBD42050	37	AE	100	0	4	0	2628	0
4	00000000B8F0E3B8	00000000B8F0E430	37	TX	131084	896	6	0	2570	0
5	00000000B8ED0B88	00000000B8ED0C00	136	TX	393216	1079	6	0	2592	1
6	00000000BBD418F8	00000000BBD41950	136	AE	100	0	4	0	2692	0
7	00007FF2363EEF90	00007FF2363EEFF0	136	TM	87361	0	3	0	2592	0

关于事务槽的描述：



关于事务槽的使用：


for update的描述：

1. SQL> select  uname  from t04209_uname where uvalue=1 for update ;
   
2. 
   
3. UNAME
   
4. ------------------------------------------------------------
   
5. aaaa1
   

复制代码

1. select  * from v_$lock  k
   
2. where k.SID in (37, 136 )
   
3. order by sid ;

复制代码

	ADDR	KADDR	SID	TYPE	ID1	ID2	LMODE	REQUEST	CTIME	BLOCK
1	00000000BBD41FF8	00000000BBD42050	37	AE	100	0	4	0	3993	0
2	00000000B8ED0B88	00000000B8ED0C00	136	TX	655374	678	6	0	5	0
3	00007FF2363EEF90	00007FF2363EEFF0	136	TM	87361	0	3	0	5	0
4	00000000BBD418F8	00000000BBD41950	136	AE	100	0	4	0	4057	0

手工锁全表，就是在没有TX锁的情况下，直接TM锁：

1. SQL> lock table t04209_uname in exclusive mode;
   
2. 
   
3. Table(s) Locked.

复制代码

	ADDR	KADDR	SID	TYPE	ID1	ID2	LMODE	REQUEST	CTIME	BLOCK
1	00000000BBD41FF8	00000000BBD42050	37	AE	100	0	4	0	5120	0
2	00007FF2363EEF90	00007FF2363EEFF0	136	TM	87361	0	6	0	11	0
3	00000000BBD418F8	00000000BBD41950	136	AE	100	0	4	0	5184	0

死锁：