SQL 优化一2Lock labs.docx
《SQL 优化一2Lock labs.docx》由会员分享，可在线阅读，更多相关《SQL 优化一2Lock labs.docx（14页珍藏版）》请在冰豆网上搜索。
SQL优化一2Locklabs
一、锁的并发与检测
---note:
execute$ORACLE_HOME/rdbms/admincatblock.sql,utllockt.sqlfirstassysdba.
createfoursession:
a、updateemployeessetsalary=salary*1.1;
b、updateemployeessetsalary=salary*1.1whereemployee_id=206;
c、updateemployeessetsalary=salary*1.1whereemployee_id=206;
d、updateemployeessetsalary=salary*1.1whereemployee_id=205;
e、findouttheblockingobjectusingnextsql
---firstexecuteutllockt.sql
selectse.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,
se.ROW_WAIT_OBJ#,
se.ROW_WAIT_FILE#,
se.ROW_WAIT_BLOCK#,
se.ROW_WAIT_ROW#,
lo.LOCKED_MODE
fromv$locked_objectlo,
all_objectso,
（selecta.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.ROW_WAIT_BLOCK#,
b.ROW_WAIT_ROW#
fromv$session_waita,v$sessionb,v$sqlareac
where（b.SQL_HASH_VALUE=c.HASH_VALUEor
b.PREV_HASH_VALUE=c.HASH_VALUE）
anda.sid=b.sid
andb.usernameisnotnull
andb.type<>'BACKGROUND'
orderbyb.logon_time）se,
（selects.sidsid,p.spidOSThread,b.nameusername,s.program
fromv$processp,v$sessions,v$bgprocessb
wherep.addr=s.paddr
andp.addr=b.paddr
UNIONALL
selects.SID,p.spidOSThread,s.usernameusername,s.program
fromv$processp,v$sessions
wherep.addr=s.paddr
ands.usernameisnotnull）p,
（selectsesswait.*
fromv$sessionse,
v$sqlareasq,
（selectlpad（'',3*（level-1））||waiting_sessionwaiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id2
fromlock_holders
connectbypriorwaiting_session=holding_session
startwithholding_sessionisnull）sesswait
where（se.PREV_HASH_VALUE=sq.HASH_VALUEor
se.SQL_HASH_VALUE=sq.HASH_VALUE）
andse.SID=sesswait.waiting_session）waitsql
wherelo.OBJECT_ID=o.object_id
andlo.SESSION_ID=se.sid（+）
andse.sid=p.sid（+）
andlo.SESSION_ID=waitsql.waiting_session（+）
orderbywaitsql.waiting_sessiondesc;
f、killsession:
altersystemkillsession'sid,serial#';
二、行锁与表锁的测试
锁的类型
查询
DML语句（C/O）
DDL语句
Select*fromdepartmentsforupdate
Select*fromdepartmentsWheredepartment_id=260forupdate
Usinglocktable…in…mode
RS
S
RX
SRX
X
Shareupdate
行锁
deletefromdepartmentswheredepartment_id=270;
√
×/√
×
×
√
√
×
√
×
×
√
Select*fromdepartmentsforupdate
√
×
×
×
×
√
√
√
√
×
√
Select*fromdepartmentsWheredepartment_id=270forupdate
√
×/√
×
×
√
√
√
√
√
×
√
表锁
共享封锁方式
Rowshare
√
√
×
√
√
√
√
√
√
×
√
Share
√
×
×
√
√
√
√
×
√
×
√
独占封锁方式
Rowexclusive
√
√
×
√
√
√
×
√
×
×
√
Sharerowexclusive
√
×
×
√
√
√
×
×
×
×
√
Exclusive
√
×
×
×
×
×
×
×
×
×
×
共享更新封锁
Shareupdate（行锁）
√
√
×
√
√
√
√
√
√
×
√
无锁
√
√
√
√
√
√
√
√
√
√
√
备注：
DML语句（C/O），C指对当前实验中的同一行数据进行操作，O指对非当前实验中的数据进行操作。
结论：
1、查询不需要锁定资源，因此对于查询语句不会产生任何锁等待。
2、DML语句需要取得共享的表锁以及独占的行锁才能执行。
3、DDL语句需要独占DDL锁才能执行。
4、Select..forupdate语句是特殊的行锁，锁定的行数取决于条件过滤，级别较高。
5、Share（S）、Sharerowexclusive（SRX）以及Exclusive（X）级别较高，不允许DML操作。
6、Shareupdate、Rowshare（RS）以及Rowexclusive（RX）级别较低，允许DML操作。
7、Exclusive锁的级别最高，不允许任何锁的操作。
实验过程：
一、行锁（exclusive）
Session1:
deletefromdepartmentswheredepartment_id=270;
Session2:
updatedepartmentssetdepartment_name='Mike'
wheredepartment_id=270;
---gotonextstepafterkillingthissession
Session3:
updatedepartmentssetdepartment_name='Mike'
wheredepartment_id=260;
---gotonextstepafterkillingthissession
Session4:
altertabledepartmentsmodifymanaged_idnumber（7）;
---gotonextstepafterkillingthissession
Session5:
Select*fromdepartmentsforupdate;
---gotonextstepafterkillingthissession
Session6:
Select*fromdepartmentsWheredepartment_id=260forupdate;
---gotonextstepafterkillingthissession
Session7:
locktabledepartmentsinrowsharemode;
---gotonextstepafterkillingthissession
Session8:
locktabledepartmentsinsharemode;
---gotonextstepafterkillingthissession
Session9:
locktabledepartmentsinrowexclusivemode;
---gotonextstepafterkillingthissession
Session10:
locktabledepartmentsinsharerowexclusivemode;
---gotonextstepafterkillingthissession
Session11:
locktabledepartmentsinexclusivemode;
---gotonextstepafterkillingthissession
Session12:
locktabledepartmentsinshareupdatemode;
---gotonextstepafterkillingthissession
二、行锁（Select*fromdepartmentsforupdate）
Session1:
Select*fromdepartmentsforupdate;
Session2:
updatedepartmentssetdepartment_name='Mike'
wheredepartment_id=270;
---gotonextstepafterkillingthissession
Session3:
updatedepartmentssetdepartment_name='Mike'
wheredepartment_id=260;
---gotonextstepafterkillingthissession
Session4:
altertabledepartmentsmodifymanaged_idnumber（7）;
---gotonextstepafterkillingthissession
Session5:
Select*fromdepartmentsforupdate;
---gotonextstepafterkillingthissession
Session6:
Select*fromdepartmentsWheredepartment_id=260forupdate;
---gotonextstepafterkillingthissession
Session7:
locktabledepartmentsinrowsharemode;
---gotonextstepafterkillingthissession
Session8:
locktabledepartmentsinsharemode;
---gotonextstepafterkillingthissession
Session9:
locktabledepartmentsinrowexclusivemode;
---gotonextstepafterkillingthissession
Session10:
locktabledepartmentsinsharerowexclusivemode;
---gotonextstepafterkillingthissession
Session11:
locktabledepartmentsinexclusivemode;
---gotonextstepafterkillingthissession
Session12:
locktabledepartmentsinshareupdatemode;
---gotonextstepafterkillingthissession
三、行锁（Select*fromdepartmentsWheredepartment_id=270forupdate）
Session1:
Select*fromdepartmentsWheredepartment_id=270forupdate;
Session2:
updatedepartmentssetdepartment_name='Mike'
wheredepartment_id=270;
---gotonextstepafterkillingthissession
Session3:
updatedepartmentssetdepartment_name='Mike'
wheredepartment_id=260;
---gotonextstepafterkillingthissession
Session4:
altertabledepartmentsmodifymanaged_idnumber（7）;
---gotonextstepafterkillingthissession
Session5:
Select*fromdepartmentsforupdate;
---gotonextstepafterkillingthissession
Session6:
Select*fromdepartmentsWheredepartment_id=260forupdate;
---gotonextstepafterkillingthissession
Session7:
locktabledepartmentsinrowsharemode;
---gotonextstepafterkillingthissession
Session8:
locktabledepartmentsinsharemode;
---gotonextstepafterkillingthissession
Session9:
locktabledepartmentsinrowexclusivemode;
---gotonextstepafterkillingthissession
Session10:
locktabledepartmentsinsharerowexclusivemode;
---gotonextstepafterkillingthissession
Session11:
locktabledepartmentsinexclusivemode;
---gotonextstepafterkillingthissession
Session12:
locktabledepartmentsinshareupdatemode;
---gotonextstepafterkillingthissession
四、表锁（Rowshare）
Session1:
locktabledepartmentsinrowsharemode;
Session2:
updatedepartmentssetdepartment_name='Mike'
wheredepartment_id=270;
---gotonextstepafterkillingthissession
Session3:
updatedepartmentssetdepartment_name='Mike'
wheredepartment_id=260;
---gotonextstepafterkillingthissession
Session4:
altertabledepartmentsmodifymanaged_idnumber（7）;
---gotonextstepafterkillingthissession
Session5:
Select*fromdepartmentsforupdate;
---gotonextstepafterkillingthissession
Session6:
Select*fromdepartmentsWheredepartment_id=260forupdate;
---gotonextstepafterkillingthissession
Session7:
locktabledepartmentsinrowsharemode;
---gotonextstepafterkillingthissession
Session8:
locktabledepartmentsinsharemode;
---gotonextstepafterkillingthissession
Session9:
locktabledepartmentsinrowexclusivemode;
---gotonextstepafterkillingthissession
Session10:
locktabledepartmentsinsharerowexclusivemode;
---gotonextstepafterkillingthissession
Session11:
locktabledepartmentsinexclusivemode;
---gotonextstepafterkillingthissession
Session12:
locktabledepartmentsinshareupdatemode;
---gotonextstepafterkillingthissession
五、表锁（share）
Session1:
locktabledepartmentsinsharemode;
Session2:
updatedepartmentssetdepartment_name='Mike'
wheredepartment_id=270;
---gotonextstepafterkillingthissession
Session3:
updatedepartmentssetdepartment_name='Mike'
wheredepartment_id=260;
---gotonextstepafterkillingthissession
Session4:
altertabledepartmentsmodifymanaged_idnumber（7）;
---gotonextstepafterkillingthissession
Session5:
Select*fromdepartmentsforupdate;
---gotonextstepafterkillingthissession
Session6:
Select*fromdepartmentsWheredepartment_id=260forupdate;
---gotonextstepafterkillingthissession
Session7:
locktabledepartmentsinrowsharemode;
---gotonextstepafterkillingthissession
Session8:
locktabledepartmentsinsharemode;
---gotonextstepafterkillingthissession
Session9:
locktabledepartmentsinrowexclusivemode;
---gotonextstepafterkillingthissession
Session10:
locktabledepartmentsinshar
升级会员 