第九讲 Oracle 数据库锁与事务2

9999Copyright©SWFC-CISD,2009.Allrightsreserved.西南林学院计算机与信息科学系——鲁宁第九讲数据库锁与事务9-9-9-9-2222Copyright©SWFC-CISD,2009.Allrightsreserved.西南林学院计算机与信息科学系——鲁宁教学目标•数据库事务处理语句–DMLDMLDMLDML语句–DDLDDLDDLDDL语句–DCLDCLDCLDCL语句•事务–CommitCommitCommitCommit–RollBackRollBackRollBackRollBack–SavePointSavePointSavePointSavePoint•锁–共享锁–独占锁9-9-9-9-3333Copyright©SWFC-CISD,2009.Allrightsreserved.西南林学院计算机与信息科学系——鲁宁数据库事务处理数据库事务处理由下面的语句组成：•DMLDMLDMLDML语句，对数据进行永久的改变•DDLDDLDDLDDL语句•DCLDCLDCLDCL语句9-9-9-9-4444Copyright©SWFC-CISD,2009.Allrightsreserved.西南林学院计算机与信息科学系——鲁宁数据库事务处理•执行第一个DMLSQLDMLSQLDMLSQLDMLSQL语句时开始•遇到下面事件之一结束：–一个COMMITCOMMITCOMMITCOMMIT或ROLLBACKROLLBACKROLLBACKROLLBACK语句被发布–一个DDLDDLDDLDDL或DCLDCLDCLDCL语句执行((((自动提交))))–用户退出iSQLiSQLiSQLiSQL****Plus/SQLPlus/SQLPlus/SQLPlus/SQL****PlusPlusPlusPlus–系统崩溃9-9-9-9-5555Copyright©SWFC-CISD,2009.Allrightsreserved.西南林学院计算机与信息科学系——鲁宁COMMITCOMMITCOMMITCOMMIT和ROLLBACKROLLBACKROLLBACKROLLBACK语句优点用COMMITCOMMITCOMMITCOMMIT和ROLLBACKROLLBACKROLLBACKROLLBACK语句，你能够::::•确保数据的一致性•在数据永久改变之前进行预览•分组逻辑相关的操作9-9-9-9-6666Copyright©SWFC-CISD,2009.Allrightsreserved.西南林学院计算机与信息科学系——鲁宁控制事务SAVEPOINTBSAVEPOINTBSAVEPOINTBSAVEPOINTBSAVEPOINTASAVEPOINTASAVEPOINTASAVEPOINTADELETEDELETEDELETEDELETEINSERTINSERTINSERTINSERTUPDATEUPDATEUPDATEUPDATEINSERTINSERTINSERTINSERTCOMMITCOMMITCOMMITCOMMIT时间TransactionTransactionTransactionTransactionROLLBACKROLLBACKROLLBACKROLLBACKtoSAVEPOINTBtoSAVEPOINTBtoSAVEPOINTBtoSAVEPOINTBROLLBACKROLLBACKROLLBACKROLLBACKtoSAVEPOINTAtoSAVEPOINTAtoSAVEPOINTAtoSAVEPOINTAROLLBACKROLLBACKROLLBACKROLLBACK9-9-9-9-7777Copyright©SWFC-CISD,2009.Allrightsreserved.西南林学院计算机与信息科学系——鲁宁事务控制9-9-9-9-8888Copyright©SWFC-CISD,2009.Allrightsreserved.西南林学院计算机与信息科学系——鲁宁UPDATE...UPDATE...UPDATE...UPDATE...SAVEPOINTupdate_done;SAVEPOINTupdate_done;SAVEPOINTupdate_done;SAVEPOINTupdate_done;Savepointcreated.Savepointcreated.Savepointcreated.Savepointcreated.Savepointcreated.Savepointcreated.Savepointcreated.Savepointcreated.INSERT...INSERT...INSERT...INSERT...ROLLBACKTOupdate_done;ROLLBACKTOupdate_done;ROLLBACKTOupdate_done;ROLLBACKTOupdate_done;Rollbackcomplete.Rollbackcomplete.Rollbackcomplete.Rollbackcomplete.Rollbackcomplete.Rollbackcomplete.Rollbackcomplete.Rollbackcomplete.回退到保存点•用SAVEPOINTSAVEPOINTSAVEPOINTSAVEPOINT语句在当前事务中创建一个保存点•用ROLLBACKTOSAVEPOINTROLLBACKTOSAVEPOINTROLLBACKTOSAVEPOINTROLLBACKTOSAVEPOINT语句回退到该保存点9-9-9-9-9999Copyright©SWFC-CISD,2009.Allrightsreserved.西南林学院计算机与信息科学系——鲁宁回退到保存点示例•deletefromdeletefromdeletefromdeletefromstudinfostudinfostudinfostudinfowherewherewherewherestudnamestudnamestudnamestudnamelike'like'like'like'李%';%';%';%';•savepointsavepointsavepointsavepointDelete_DoneDelete_DoneDelete_DoneDelete_Done;;;;•updateupdateupdateupdatestudinfostudinfostudinfostudinfosetsetsetsetstudnamestudnamestudnamestudname='='='='刘'||substr(studname,2)where'||substr(studname,2)where'||substr(studname,2)where'||substr(studname,2)wherestudnamestudnamestudnamestudnamelike'like'like'like'王%';%';%';%';•savepointsavepointsavepointsavepointUpdate_DoneUpdate_DoneUpdate_DoneUpdate_Done;;;;•RollBackRollBackRollBackRollBacktotototoDelete_DoneDelete_DoneDelete_DoneDelete_Done;;;;•RollBackRollBackRollBackRollBack;;;;9-9-9-9-10101010Copyright©SWFC-CISD,2009.Allrightsreserved.西南林学院计算机与信息科学系——鲁宁隐式事务处理•在下面的情况下，一个自动提交发生：–DDLDDLDDLDDL语句被发送–DCLDCLDCLDCL语句被发送–正常退出iSQLiSQLiSQLiSQL****Plus/SQLPlus/SQLPlus/SQLPlus/SQL****PlusPlusPlusPlus，没有明确地发送COMMITCOMMITCOMMITCOMMIT或ROLLBACKROLLBACKROLLBACKROLLBACK语句•当iSQLiSQLiSQLiSQL****PlusPlusPlusPlus非正常退出时，或者发生系统故障时，一个自动回退发生9-9-9-9-11111111Copyright©SWFC-CISD,2009.Allrightsreserved.西南林学院计算机与信息科学系——鲁宁COMMITCOMMITCOMMITCOMMIT或ROLLBACKROLLBACKROLLBACKROLLBACK之前数据的状态•以前的数据状态能够被恢复•当前用户能用SELECTSELECTSELECTSELECT语句查看DMLDMLDMLDML操作的结果•其他用户不能观察当前用户DMLDMLDMLDML语句的结果•受影响的行被锁定；•其他用户不能改变受影响的行中数据9-9-9-9-12121212Copyright©SWFC-CISD,2009.Allrightsreserved.西南林学院计算机与信息科学系——鲁宁在COMMITCOMMITCOMMITCOMMIT之后数据的状态•数据在数据库中被永久地改变....•数据的以前状态被永久地丢失•所有用户都能观察该结果•受影响行的锁定被释放；•其它用户可以操纵那些行•所有保存点被擦除9-9-9-9-13131313Copyright©SWFC-CISD,2009.Allrightsreserved.西南林学院计算机与信息科学系——鲁宁提交改变•产生改变•提交改变COMMIT;COMMIT;COMMIT;COMMIT;Commitcomplete.Commitcomplete.Commitcomplete.Commitcomplete.Commitcomplete.Commitcomplete.Commitcomplete.Commitcomplete.DELETEFROMemployeesDELETEFROMemployeesDELETEFROMemployeesDELETEFROMemployeesWHEREemployee_id=99999;WHEREemployee_id=99999;WHEREemployee_id=99999;WHEREemployee_id=99999;1rowdeleted.1rowdeleted.1rowdeleted.1rowdeleted.INSERTINTOdepartmentsINSERTINTOdepartmentsINSERTINTOdepartmentsINSERTINTOdepartmentsVALUES(290,'CorporateTax',NULL,1700);VALUES(290,'CorporateTax',NULL,1700);VALUES(290,'CorporateTax',NULL,1700);VALUES(290,'CorporateTax',NULL,1700);1rowinserted.1rowinserted.1rowinserted.1rowinserted.DELETEFROMemployeesDELETEFROMemployeesDELETEFROMemployeesDELETEFROMemployeesWHEREemployee_id=99999;WHEREemployee_id=99999;WHEREemployee_id=99999;WHEREemployee_id=99999;WHEREemployee_id=99999;WHEREemployee_id=99999;WHEREemployee_id=99999;WHEREemployee_id=99999;1rowdeleted.1rowdeleted.1rowdeleted.1rowdeleted