SQL 优化一2Lock labs.docx

1、SQL 优化一2Lock labs一、锁的并发与检测-note:execute $ORACLE_HOME/rdbms/admin catblock.sql,utllockt.sql first as sysdba. create four session: a、update employees set salary=salary*1.1; b、update employees set salary=salary*1.1 where employee_id = 206; c、update employees set salary=salary*1.1 where employee_id = 20

2、6; d、update employees set salary=salary*1.1 where employee_id = 205; e、find out the blocking object using next sql -first execute utllockt.sql select se.SID, se.SERIAL#, p.osthread, se.sql_text, waitsql.*, p.username, p.program, o.object_name, se.event, se.state, se.seconds_in_wait, se.logon_time, s

3、e.ROW_WAIT_OBJ#, se.ROW_WAIT_FILE#, se.ROW_WAIT_BLOCK#, se.ROW_WAIT_ROW#, lo.LOCKED_MODE from v$locked_object lo, all_objects o, ( select a.sid, b.serial#, c.SQL_TEXT, a.state, a.wait_time, a.seconds_in_wait, b.username, b.osuser, b.logon_time, b.saddr, a.EVENT, b.ROW_WAIT_OBJ#, b.ROW_WAIT_FILE#, b.

4、ROW_WAIT_BLOCK#, b.ROW_WAIT_ROW# from v$session_wait a, v$session b, v$sqlarea c where ( b.SQL_HASH_VALUE = c.HASH_VALUE or b.PREV_HASH_VALUE = c.HASH_VALUE) and a.sid = b.sid and b.username is not null and b.type BACKGROUND order by b.logon_time) se, ( select s.sid sid, p.spid OSThread, b.name user

5、name, s.program from v$process p, v$session s, v$bgprocess b where p.addr = s.paddr and p.addr = b.paddr UNION ALL select s.SID, p.spid OSThread, s.username username, s.program from v$process p, v$session s where p.addr = s.paddr and s.username is not null) p, (select sesswait.* from v$session se, v

6、$sqlarea sq, (select lpad( , 3 * (level - 1) | waiting_session waiting_session, lock_type, mode_requested, mode_held, lock_id1, lock_id2 from lock_holders connect by prior waiting_session = holding_session start with holding_session is null) sesswait where (se.PREV_HASH_VALUE = sq.HASH_VALUE or se.S

7、QL_HASH_VALUE = sq.HASH_VALUE) and se.SID = sesswait.waiting_session) waitsql where lo.OBJECT_ID = o.object_id and lo.SESSION_ID = se.sid(+) and se.sid = p.sid(+) and lo.SESSION_ID = waitsql.waiting_session(+) order by waitsql.waiting_session desc ; f、kill session:alter system kill session sid,seria

8、l#;二、行锁与表锁的测试锁的类型查询DML语句(C/O) DDL语句Select * from departments for updateSelect * from departments Where department_id=260 for updateUsing lock table in modeRSSRXSRXXShare update行锁delete from departments where department_id = 270;/Select * from departments for updateSelect * from departments Where dep

9、artment_id=270 for update/表锁共享封锁方式Row shareShare独占封锁方式Row exclusiveShare row exclusiveExclusive共享更新封锁Share update(行锁) 无锁备注：DML语句(C/O) ，C指对当前实验中的同一行数据进行操作，O指对非当前实验中的数据进行操作。结论： 1、查询不需要锁定资源，因此对于查询语句不会产生任何锁等待。 2、DML语句需要取得共享的表锁以及独占的行锁才能执行。 3、DDL 语句需要独占DDL锁才能执行。 4、Select . for update 语句是特殊的行锁，锁定的行数取决于条件过滤

10、，级别较高。 5、Share（S）、Share row exclusive （SRX）以及Exclusive（X）级别较高，不允 许DML操作。 6、Share update、Row share（RS）以及Row exclusive（RX）级别较低，允许DML 操作。 7、Exclusive锁的级别最高，不允许任何锁的操作。实验过程：一、 行锁（exclusive）Session 1: delete from departments where department_id = 270;Session 2: update departments set department_name = Mik

11、ewhere department_id = 270;-go to next step after killing this sessionSession 3: update departments set department_name = Mikewhere department_id = 260;-go to next step after killing this sessionSession 4: alter table departments modify managed_id number(7);-go to next step after killing this sessio

12、n Session 5: Select * from departments for update;-go to next step after killing this sessionSession 6: Select * from departments Where department_id=260 for update;-go to next step after killing this sessionSession 7: lock table departments in row share mode;-go to next step after killing this sess

13、ionSession 8: lock table departments in share mode;-go to next step after killing this sessionSession 9: lock table departments in row exclusive mode;-go to next step after killing this sessionSession 10: lock table departments in share row exclusive mode;-go to next step after killing this sessionS

14、ession 11: lock table departments in exclusive mode;-go to next step after killing this sessionSession 12: lock table departments in share update mode;-go to next step after killing this session二、 行锁（Select * from departments for update）Session 1: Select * from departments for update;Session 2: upda

15、te departments set department_name = Mikewhere department_id = 270;-go to next step after killing this sessionSession 3: update departments set department_name = Mikewhere department_id = 260;-go to next step after killing this sessionSession 4: alter table departments modify managed_id number(7);-g

16、o to next step after killing this session Session 5: Select * from departments for update;-go to next step after killing this sessionSession 6: Select * from departments Where department_id=260 for update;-go to next step after killing this sessionSession 7: lock table departments in row share mode;

17、-go to next step after killing this sessionSession 8: lock table departments in share mode;-go to next step after killing this sessionSession 9: lock table departments in row exclusive mode;-go to next step after killing this sessionSession 10: lock table departments in share row exclusive mode;-go

18、to next step after killing this sessionSession 11: lock table departments in exclusive mode;-go to next step after killing this sessionSession 12: lock table departments in share update mode;-go to next step after killing this session三、 行锁（Select * from departments Where department_id=270 for update

19、）Session 1: Select * from departments Where department_id=270 for update;Session 2: update departments set department_name = Mikewhere department_id = 270;-go to next step after killing this sessionSession 3: update departments set department_name = Mikewhere department_id = 260;-go to next step aft

20、er killing this sessionSession 4: alter table departments modify managed_id number(7);-go to next step after killing this session Session 5: Select * from departments for update;-go to next step after killing this sessionSession 6: Select * from departments Where department_id=260 for update;-go to

21、next step after killing this sessionSession 7: lock table departments in row share mode;-go to next step after killing this sessionSession 8: lock table departments in share mode;-go to next step after killing this sessionSession 9: lock table departments in row exclusive mode;-go to next step after

22、 killing this sessionSession 10: lock table departments in share row exclusive mode;-go to next step after killing this sessionSession 11: lock table departments in exclusive mode;-go to next step after killing this sessionSession 12: lock table departments in share update mode;-go to next step afte

23、r killing this session四、 表锁（Row share） Session 1: lock table departments in row share mode;Session 2: update departments set department_name = Mike where department_id = 270;-go to next step after killing this sessionSession 3: update departments set department_name = Mikewhere department_id = 260;-

24、go to next step after killing this sessionSession 4: alter table departments modify managed_id number(7);-go to next step after killing this sessionSession 5: Select * from departments for update;-go to next step after killing this sessionSession 6: Select * from departments Where department_id=260

25、for update;-go to next step after killing this sessionSession 7: lock table departments in row share mode;-go to next step after killing this sessionSession 8: lock table departments in share mode;-go to next step after killing this sessionSession 9: lock table departments in row exclusive mode;-go

26、to next step after killing this sessionSession 10: lock table departments in share row exclusive mode;-go to next step after killing this sessionSession 11: lock table departments in exclusive mode;-go to next step after killing this sessionSession 12: lock table departments in share update mode;-go

27、 to next step after killing this session五、 表锁（share） Session 1: lock table departments in share mode;Session 2: update departments set department_name = Mike where department_id = 270;-go to next step after killing this sessionSession 3: update departments set department_name = Mikewhere department_

28、id = 260;-go to next step after killing this sessionSession 4: alter table departments modify managed_id number(7);-go to next step after killing this sessionSession 5: Select * from departments for update;-go to next step after killing this sessionSession 6: Select * from departments Where departme

29、nt_id=260 for update;-go to next step after killing this sessionSession 7: lock table departments in row share mode;-go to next step after killing this sessionSession 8: lock table departments in share mode;-go to next step after killing this sessionSession 9: lock table departments in row exclusive mode;-go to next step after killing this sessionSession 10: lock table departments in shar