《Linux与数据库》课件6. 事务处理与锁.pptx_163文库

资源描述

1、什么是事务什么是事务若干操作的集合，这些操作要么都完成要么都取消事务举例事务举例-超市付款超市付款在超市买了一盒牙膏(5元)，一袋洗衣粉(10元)，最后付款，超市收银系统需要依次执行下面操作牙膏库存量-1 洗衣粉库存量-1 你的银行卡余额-15 超市账户余额+15 以上四个操作要么都完成，要么都取消事务在事务在DBMS中处于核心地位中处于核心地位并发控制以事务为单位数据库恢复以事务为单位理论和实践主要理论和实践主要贡献者贡献者-James Gray 1944.1.12，生于加州旧金山 19611966，BS，UCB，数学 Bell实验室，参与Multics项目 1969，PhD，UC

2、B，程序语言 1973，IBM，参与System R项目 1995，Microsoft 因数据库和事务处理的贡献，获1998年度图灵奖 2007-01-28，失踪James Gray事务的事务的ACID属性属性 Atomicity Consistency,Correctness(C.J.Date)Isolation Durability事务的两种基本操作事务的两种基本操作 commit 把重做缓冲区的数据写入重做文件释放事务中的锁把commit操作的scn号写入重做文件(Oracle)rollback 把undo表空间中的旧数据替换新数据，撤销事务中的操作效果事务提交模式事务提交模式默认

3、为自动提交autocommit=on 查看autocommit当前值mysql show variables like AUTOCOMMIT;设置autocommit(on/off或1/0)mysql set autocommit=on;显式开始事务mysql start transaction;mysql begin work;说明：begin和begin work是start transaction的别名,执行以上任意一个命令开启事务，都会自动先执行commit 开启隐式事务模式mysql set autocommit=0;演示默认设置情况下的事务演示默认设置情况下的事务 commit的效

4、果 rollback的效果同一事务多次读取数据的特点DDL及及DCL等语句对事务的影响等语句对事务的影响会在执行DDL或DCL等语句的前后自动执行commit*自动自动commit的语句的语句-DDL ALTER EVENT,FUNCTION,PROCEDURE,SERVER,TABLE,VIEW CREATE DATABASE,EVENT,FUNCTION,INDEX,PROCEDURE,ROLE,SERVER,SPATIAL REFERENCE SYSTEM,TABLE,TRIGGER,VIEW DROP DATABASE,EVENT,FUNCTION,INDEX,PROCEDURE,

5、ROLE,SERVER,SPATIAL REFERENCE SYSTEM,TABLE,TRIGGER,VIEW TRUNCATE TABLE INSTALL PLUGIN RENAME TABLE UNINSTALL PLUGIN*自动自动commit的语句的语句-DCL(修改修改mysql系统数据库系统数据库)ALTER USER CREATE USER DROP USER GRANT RENAME USER REVOKE SET PASSWORD*自动自动commit的语句的语句-事务和锁控制语句事务和锁控制语句 BEGIN/START TRANSACTION LOCK/UNLOCK TA

6、BLES SET autocommit=1(if the value is not already 1)*自动自动commit的语句的语句-数据装载语句数据装载语句 LOAD DATA INFILE.LOAD DATA INFILE causes an implicit commit only for tables using the NDB storage engine.*自动自动commit的语句的语句-复制控制语句复制控制语句 START SLAVE STOP SLAVE RESET SLAVE CHANGE MASTER TO*自动自动commit的语句的语句-管理语句管理语句 ANA

7、LYZE TABLE CACHE INDEX CHECK TABLE FLUSH LOAD INDEX INTO CACHE OPTIMIZE TABLE REPAIR TABLE RESET(but not RESET PERSIST)事务隔离级别事务隔离级别 InnoDB支持SQL-92的四种隔离级别 read uncommitted:可读取到其他连接未提交的修改结果 read committed：只能读取到其他连接提交后的修改结果 repeatable read：事务内的两次相同查询的查询结果相同，读取到的结果都是第一次读：事务内的两次相同查询的查询结果相同，读取到的结果都是第一次读取时

8、的提交状态，但可以取时的提交状态，但可以update或或delete其他连接提交的修改结果其他连接提交的修改结果 serializable：事务内的两次相同查询的查询结果相同，事务内的两次相同查询的查询结果相同，对读取到的行都加锁默认为repeatable read，会产生幻像读(phantom read)read committed和repeatable read的读取操作不会使用锁 serializable级别对读取到的行都加锁，不会产生幻像读设置事务隔离级别设置事务隔离级别使用set命令set global transaction isolation level read commi

9、tted;set session transaction isolation level read committed;set global transaction_isolation=REPEATABLE-READ;set session transaction_isolation=SERIALIZABLE;set persist transaction_isolation=repeatable read;set transaction_isolation=read-committed;(推荐方式)在配置文件/etc/f中设置(全局修改)mysqldtransaction-isolation

11、Value|+-+-+|transaction_isolation|REPEATABLE-READ|+-+-+隔离级别特性隔离级别特性并发控制要解决的问题并发控制要解决的问题*丢失更新脏读不可重复读*丢失更新示例丢失更新示例为了促销，超市要降低某商品的价格，降价条件有两个：若其当前价格高于 100 元，则把价格调整为原来的 1/2(即五折)若库存量高于 1000，则把价格调整为原来的 4/5(即八折)两个条件是独立的，如果两个条件都满足，价格五折后，再八折如某商品当前价格为 200，库存量 1500，则降价的两个条件均满足，五折后，其价格降低至 100，再八折后，其价格降低至 80，这

12、样其最后价格为 80。*丢失更新示例丢失更新示例若工作人员 A 依据第一个条件调价，工作人员 B 依据第二个条件调价，分别使用两个连接，操作顺序如下：*丢失更新示例丢失更新示例两个工作人员各自完成上述操作后，工作人员 B 把 A 的修改结果覆盖了，导致最后价格不是 80，而是 160。虽然两个工作人员的操作步骤都是正确的，因为没有合适的并发控制，导致最后的结果是错误的。脏读脏读脏读是指一个连接读取了其他尚未提交的事务修改的数据。脏读破坏了事务的原子性，读取到的是事务进行过程中的中间结果，若此结果与事务结束时的结果不同，则此连接读取的是错误数据，如果以此错误数据为依据继续执行另外的任务，则

13、可能造成一连串的错误。脏读示例脏读示例一顾客购买了 2 支钢笔、10 个笔记本，总价 100 元，收银员在连接 A 完成收银操作，超市采购员在连接 B 查询商品库存，确定某种商品是否需要进货。假定收银开始前，钢笔库存量为 100，笔记本库存量为 300，下面是两个连接在不同时间进行的操作：不可重复读不可重复读不可重复读是指一个事务中的查询操作因为分为多个步骤，导致其结果既包括了某个事务开始之前的数据，也包括了这个事务开始之后的数据，从而在最后得到了错误的查询结果。不可重复读示例不可重复读示例各帐号初始值不可重复读示例不可重复读示例用户 A 查询银行三个账号的余额总和。用户 B 由 ac

14、c3 账号转账 100 至 acc1。对三个并发问题的解决方法对三个并发问题的解决方法丢失更新：通过锁解决(select.for update)。脏读问题：通过多版本数据解决(隔离级别避免设置为read uncommitted)。不可重复读：通过多版本数据解决(隔离级别设置为repeatable read)。并发控制技术并发控制技术锁 InnoDB使用行锁多版本数据 read committed和repeatable read使用 read uncommitted和serializable不使用 read uncommitted级别下，直接读取新版本数据 serializable级别下，

15、对所有读到的行加锁多版本数据的测试多版本数据的测试锁锁锁是用来控制访问共享资源的一种机制目的是把并发操作串行化锁的两种基本模式锁的两种基本模式 shared exclusiveintention lock 意向共享锁：select.for share 获得行的共享锁之前，先要获得表的IS锁意向排他锁：select.for update 获得行的排他锁之前，先要获得表的IX锁锁模式锁模式表锁执行lock tables t write/read;unlock tables;record lock 在索引记录加的锁 gap lock 只存在于repeatable read隔离级别下的辅助索

16、引中，锁定一个范围对相邻两个索引记录之间的gap加的锁 gap锁是共享的，只阻止其他事务向gap内添加记录，不阻止其他事务对其附加gap锁 gap X-lock 和 gap S-lock 的效果相同 next-key lock record lock+gap lock 只在repeatable read隔离级别使用，以防止phantom record insert intention lock 执行insert操作之前附加的gap锁锁模式锁模式元数据锁元数据锁由create,alter,drop操作产生 MySQL 5.5引入update和和delete操作产生的行锁操作产生的行锁执行

20、emp|TABLE|IX|GRANTED|10049:13:4:2|law|emp|RECORD|X|GRANTED|+-+-+-+-+-+-+连接3：mysql update emp set sal=3000 where empno=7369;查询锁信息查询锁信息-data_locks连接2：mysql select engine_lock_id,object_schema,object_name,-lock_type,lock_mode,lock_status -from performance_schema.data_locks;+-+-+-+-+-+-+|engine_lock_id|

21、object_schema|object_name|lock_type|lock_mode|lock_status|+-+-+-+-+-+-+|10054:1070|law|emp|TABLE|IX|GRANTED|10054:13:4:2|law|emp|RECORD|X|WAITING|10049:1070|law|emp|TABLE|IX|GRANTED|10049:13:4:2|law|emp|RECORD|X|GRANTED|+-+-+-+-+-+-+performance_schema.data_locks ENGINE_LOCK_ID 表锁的第二个数字表示table_id 行锁的

22、中间3个数字：表空间编号(space_id)，页号，行号最后一个数字即OBJECT_INSTANCE_BEGIN的值，即锁在内存的地址 ENGINE_TRANSACTION_ID 要求锁的事务id，由information_schema.innodb_trx可得到此事务的更多信息，如锁住的行数，锁占用的内存，隔离级别 thread_id 根据此thread_id查询performance_schema.threads，可以得到连接的更多信息，列PROCESSLIST_INFO是此连接正在执行的SQL OBJECT_INSTANCE_BEGIN 锁在内存中的地址 LOCK_DATA 锁住的主键

23、索引值锁住的普通索引值，附加主键索引值对于record锁，若无主键索引，无唯一索引，则为rowid*查询等待信息查询等待信息-data_lock_waitsmysql select requesting_engine_lock_id,blocking_engine_lock_id -from performance_schema.data_lock_waits;+-+-+|requesting_engine_lock_id|blocking_engine_lock_id|+-+-+|10054:13:4:2|10049:13:4:2|+-+-+mysql select trx_id,trx

24、_state,trx_requested_lock_id,-trx_tables_locked,trx_rows_locked -from information_schema.innodb_trx;+-+-+-+-+-+|trx_id|trx_state|trx_requested_lock_id|trx_tables_locked|trx_rows_locked|+-+-+-+-+-+|10054|LOCK WAIT|10054:13:4:2|1|1|10049|RUNNING|NULL|1|1|+-+-+-+-+-+*innodb_lock_wait_timeout 用于设置等待锁释放的

25、秒数，默认为50mysql update emp set sal=4000 where empno=7369;ERROR 1205(HY000):Lock wait timeout exceeded;try restarting transaction*SHOW ENGINE INNODB STATUS 在显示内容的TRANSACTIONS部分会列出锁的信息-TRANSACTIONS-Trx id counter 4464Purge done for trxs n:o 4462 undo n:o show variables like innodb_deadlock_detect;mysql

26、show variables like innodb_lock_wait_timeout;设置innodb_deadlock_detect开启死锁探测，会降低性能。依赖innodb_lock_wait_timeout探测死锁会更有效率锁锁-read committed隔离级别隔离级别普通查询不加锁连接1：mysql create table t(a int,b int);mysql insert into t values(1,10),(2,20),(3,30),(4,40),(5,50);mysql show variables like transaction_isolation;-RE

27、AD-COMMITTEDmysql begin;mysql select*from t where a=1;连接2：mysql select engine_lock_id,object_schema,object_name,-lock_type,lock_mode,lock_status -from performance_schema.data_locks;Empty set(0.00 sec)锁锁-read committed隔离级别隔离级别 update对表附加IX锁，对行附加X锁连接1：mysql update t set b=100 where a=1;连接2：mysql selec

29、+-+锁锁-repeatable read 删除t表的索引(若存在)mysql drop index idx_a on t;普通查询不加锁普通查询不会阻止其他连接对表t执行insert和update操作锁锁-repeatable read 无索引时，update操作相当于锁住整个表mysql begin;mysql update t set b=100 where a=1;mysql select engine_lock_id,object_name,index_name,-lock_type,lock_mode,lock_status from performance_schema.dat

30、a_locks;+-+-+-+-+-+-+|engine_lock_id|object_name|index_name|lock_type|lock_mode|lock_status|+-+-+-+-+-+-+|19113:1165|t|NULL|TABLE|IX|GRANTED|19113:92:4:1|t|GEN_CLUST_INDEX|RECORD|X|GRANTED|19113:92:4:2|t|GEN_CLUST_INDEX|RECORD|X|GRANTED|19113:92:4:3|t|GEN_CLUST_INDEX|RECORD|X|GRANTED|19113:92:4:4|t|

31、GEN_CLUST_INDEX|RECORD|X|GRANTED|19113:92:4:5|t|GEN_CLUST_INDEX|RECORD|X|GRANTED|19113:92:4:6|t|GEN_CLUST_INDEX|RECORD|X|GRANTED|+-+-+-+-+-+-+锁锁-repeatable read存在普通索引时，附加gap锁mysql create index idx_a on t(a);mysql begin;Query OK,0 rows affected(0.00 sec)mysql update t set b=100 where a=1;mysql select

35、接1：mysql create table t(a int,b int);mysql insert into t values(1,10),(2,20),(3,30),(4,40),(5,50);mysql begin;mysql select*from t where a=1;连接2：mysql select engine_lock_id,object_schema,object_name,lock_type,lock_mode,lock_status -from performance_schema.data_locks;+-+-+-+-+-+-+|engine_lock_id|objec

36、t_schema|object_name|lock_type|lock_mode|lock_status|+-+-+-+-+-+-+|421982027832056:1141|db|t|TABLE|IS|GRANTED|421982027832056:80:4:1|db|t|RECORD|S|GRANTED|421982027832056:80:4:2|db|t|RECORD|S|GRANTED|421982027832056:80:4:3|db|t|RECORD|S|GRANTED|421982027832056:80:4:4|db|t|RECORD|S|GRANTED|4219820278

37、32056:80:4:5|db|t|RECORD|S|GRANTED|421982027832056:80:4:6|db|t|RECORD|S|GRANTED|+-+-+-+-+-+-+7 rows in set(0.00 sec)*锁锁-serializable在a列附加普通索引后mysql select engine_lock_id,object_schema,object_name,-lock_type,lock_mode,lock_status -from performance_schema.data_locks;+-+-+-+-+-+-+|engine_lock_id|object

38、_schema|object_name|lock_type|lock_mode|lock_status|+-+-+-+-+-+-+|421982027832056:1141|db|t|TABLE|IS|GRANTED|421982027832056:80:5:2|db|t|RECORD|S|GRANTED|421982027832056:80:4:2|db|t|RECORD|S|GRANTED|421982027832056:80:5:3|db|t|RECORD|S,GAP|GRANTED|+-+-+-+-+-+-+*锁锁-serializable在a列附加唯一索引后mysql select

39、engine_lock_id,object_schema,object_name,-lock_type,lock_mode,lock_status -from performance_schema.data_locks;+-+-+-+-+-+-+|engine_lock_id|object_schema|object_name|lock_type|lock_mode|lock_status|+-+-+-+-+-+-+|421982027832056:1142|db|t|TABLE|IS|GRANTED|421982027832056:81:4:2|db|t|RECORD|S|GRANTED|+

40、-+-+-+-+-+-+2 rows in set(0.00 sec)唯一索引下，不同隔离级别下唯一索引下，不同隔离级别下update产生的锁相同产生的锁相同对表附加IX锁对索引记录附加X锁mysql select engine_lock_id,object_schema,object_name,-lock_type,lock_mode,lock_status -from performance_schema.data_locks;+-+-+-+-+-+-+|engine_lock_id|object_schema|object_name|lock_type|lock_mode|lock_

41、status|+-+-+-+-+-+-+|10625:1142|db|t|TABLE|IX|GRANTED|10625:81:4:2|db|t|RECORD|X|GRANTED|+-+-+-+-+-+-+2 rows in set(0.00 sec)说明：以上结论也适用于select.for update*执行执行show engine innodb status查看锁查看锁开启下面两个参数 innodb_status_output innodb_status_output_locks 执行show engine innodb status 查看其transactions部分*总结：不同操作

42、产生的锁总结：不同操作产生的锁 SELECT.FROM is a consistent read,reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE.For SERIALIZABLE level,the search sets shared next-key locks on the index records it encounters.However,only an index record lock is

43、required for statements that lock rows using a unique index to search for a unique row.SELECT.FROM.FOR SHARE sets shared next-key locks on all index records the search encounters.However,only an index record lock is required for statements that lock rows using a unique index to search for a unique r

44、ow.SELECT.FROM.FOR UPDATE sets an exclusive next-key lock on every record the search encounters.However,only an index record lock is required for statements that lock rows using a unique index to search for a unique row.*总结：不同操作产生的锁总结：不同操作产生的锁(续续)UPDATE.WHERE.sets an exclusive next-key lock on every

45、 record the search encounters.However,only an index record lock is required for statements that lock rows using a unique index to search for a unique row.DELETE FROM.WHERE.sets an exclusive next-key lock on every record the search encounters.However,only an index record lock is required for statements that lock rows using aunique index to search for a unique row INSERT sets an exclusive lock on the inserted row.This lock is an index-record lock,not a next-key lock(that is,there is no gap lock)and does not prevent other sessions from inserting into the gap before the inserted row

展开阅读全文