上海交通大学 数据库原理 陆朝俊 课件10SQL2概要1.pptVIP

Lu Chaojun, SJTU * Example: Non-repeatable Read T1 T2 SELECT name FROM S WHERE sno=007; UPDATE S SET name = Chow WHERE sno=007; COMMIT; SELECT name FROM S WHERE sno=007; Lu Chaojun, SJTU * Isolation Level: RR SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; No “non-repeatable read”; but Phantom: later query may see “phantom” tuples, i.e. the result of insertions from other transactions. Example: Phantom Lu Chaojun, SJTU * T1 T2 SELECT name FROM S WHERE age 18; insert into S values (0,X,15,CS); COMMIT; SELECT name FROM S WHERE age 18; Lu Chaojun, SJTU * Isolation Level: Serializable SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SQL default No dirty read; repeatable; no phantom. Lu Chaojun, SJTU * Summary of Isolation Levels Weaker isolation levels increase performance by eliminating overhead and increasing concurrency. isolation dirty Nonrepeatable phantom default level read read access mode RU Y Y Y Readonly RC N Y Y R/W RR N N Y R/W S N N N R/W End Lu Chaojun, SJTU Lu Chaojun, SJTU Lu Chaojun, SJTU * More SQL Database Modifications Transactions Lu Chaojun, SJTU * DB Modifications A modification command does not return a result (as a query does), but changes the database in some way. Three kinds of modifications: Insert a tuple or tuples. Delete a tuple or tuples. Update the value(s) of an existing tuple or tuples. Lu Chaojun, SJTU *