电工与电子技术电子商务电子课件数据库原理及应用教程（基于Linux的MySQL和NoSQL应用）第14章ppt

1、第14章 事务与MySQL的多用户并发控制数据库原理及应用教程（基于Linux的MySQL和NoSQL应用）CONTENTS事务1MySQL的并发控制2小结3访问控制114.1.1 事务的概念对于一般简单的业务逻辑或中小型程序而言，无须考虑应用MySQL事务。在现实生活中，事务就在我们周围银行交易、股票交易、网上购物、库存品控制，到处都有这种情况发生。在所有这些例子中，事务的成功取决于这些相互依赖的行为是否能够被成功地执行，是否互相协调。其中的任何一个失败将取消整个事务，系统返回到事务处理以前的状态。事务的原子性事务通常包含一系列更新操作（update、insert和delete等操作语句），

2、这些更新操作是一个不可分割的逻辑工作单元。14.1.2 事务的ACID特性，每个事务的处理必须满足ACID原则，即原子性(A)、一致性(C)、隔离性(I)和持久性(D)。每个事务都必须被认为是一个不可分割的单元。假设一个事务由两个或者多个任务组成，其中的语句必须同时成功才能认为事务是成功的。如果事务失败，系统将会返回到事务以前的状态。原子性(A)不管事务是完全成功完成还是中途失败，当事务使系统处于一致的状态时存在一致性。在MySQL中，一致性主要由MySQL的日志机制处理，它记录了数据库的所有变化，为事务恢复提供了跟踪记录。一致性(C)14.1.2 事务的ACID特性指每个事务在它自己的空间发

3、生，和其他发生在系统中的事务隔离，而且事务的结果只有在它完全被执行时才能看到。隔离性(I)指即使系统崩溃，一个提交的事务仍然存在。当一个事务完成，数据库的日志已经被更新时，持久性就开始发生作用。MySQL通过保存一条记录事务过程中系统变化的二进制事务日志文件来实现持久性。持久性(D)14.1.2 事务的ACID特性假设数据有两个域，A和B，在两个记录里。一个完整约束需要A值和B值必须相加得100。下面以SQL代码创建上面描述的表：create table acidtest (A integer b integer check(A十 B = 100);考虑这两个事务，T1从A转移10到B。T2从

4、B转移10到A。为完成这两个事物，一共有4个步骤：(l)从A减IO;(2)加10到B；(3)从B减10;(4)加10到A。如果T1在一半的时候失败，那么数据库会消除了T1的效果，并且T2只能看见有效数据。事务的执行可能交叉，实际执行顺序可能是：A-10，B-10，B+10，A+10。如果T1失败，T2不能看到T1的中间值，因此T1必须回滚。14.1.3 MySQL事务控制语句MySQL中可以使用begin 开始事务，使用commint结束事务，中间可以使用rollback回滚事务。MySQL通过set autocommint 、start transaction 、commit和rollbac

5、k等语句支持本地事务。start transaction | begin work commit work and no chain no release rollback work and no chain no release set autocommit = 0 | 1 14.1.4 事务的隔离性级别每一个事务都有一个所谓的隔离级，它定义了用户彼此之间隔离和交互的程度。如果没有事务的隔离性，不同的SELECT语句将会在同一个事务的环境中检索到不同的结果，因为在这期间，基本上数据已经被其他事务所修改。这将导致不一致性，同时很难相信结果集，从而不能利用查询结果作为计算的基础。MySQL提供了

6、下面4种隔离级：序列化（serializable）、可重复读(repeatable read)、提交读(read committed)、未提交读（read uncommitted)只有支持事务的和存储引擎（比如，InnoDB）才可以定义一个隔离级。14.1.4 事务的隔离性级别定义隔离级可以使用set transaction 语句。其中语句中如果指定global，那么定义的隔离级将适用于所有的sql用户；如果指定session，则隔离级只适用于当前运行的会话和连接。MySQL默认为repeatable read隔离级。set global|session transaction isolati

7、on level seriaizable (1)序列化如果隔离级为序列化，用户之间通过一个接一个顺序地执行当前的事务提供了事务之间最大限度的隔离。14.1.4 事务的隔离性级别set global|session transaction isolation level repeatable read (2)可重复读如果用户在同一个事务中执行同条select语句数次，结果总是相同的set global|session transaction isolation level read committed (3)提交读read committed隔离级的安全性比repeatable read隔离级的

8、安全性要差。在事务处理期间，如果其他事务修改了相应的表，那么同一个事务的多个select语句可能返回不同的结果.14.1.4 事务的隔离性级别set global|session transaction isolation level read uncommitted (4)未提交读提供了事务之间最小限度的隔离。select tx_isolation; 结果：tx_isolation repeatable-read系统变量tx_isolation中存储了事务的隔离级，可以使用select随时获得当前隔离级的值： MySQL的并发控制2在单处理机系统中，事务的并行执行实际上是这些并行事务轮流交叉

9、进行，这种并行执行方式称为交叉并发方式在多处理机系统中，每个处理机可以运行一个事务，多个处理机可以同时运行多个事务，实现事务真正的并发运行，这种并发执行方式称为同时并发方式14.2.1 并发概述当多个用户并发地存取数据库时就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制可能会存取和存储不正确的数据，就会出现数据的不一致问题。当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题最后的更新覆盖了由其他事务所做的更新。1、丢失更新(lost update) 问题一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的

10、数据就处于不一致状态；这时，另-个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未交的数据依赖关系2、脏读(dirty read)问题14.2.1 并发概述当一个事务多次访问同一行而且每次读取不同的数据时，会发生不可重复读( unrepeatableread)问题。3、不可重复读( unrepeatableread) 问题当一个事务对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻读(phantom read)问题。事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其他事务删除。同样，由于

11、其他事务的插入操作，事务的第二次读或后续读显示有一行已不存在于原始读中。4、幻读(phantom read) 问题14.1.2 锁的概述当用户对数据库并发访问时，为了确保事务完整性和数据库一致性，需要使用锁定，它是实现数据库并发控制的主要手段。锁一种用来防止多个客户端同时访问数据而产生问题的机制。1)表级锁：一个特殊类型的访问，整个表被客户锁定。根据锁定的类型，其他客户不能向表中插入记录，甚至从中读数据也受到限制。其特点是：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突的概率最高，并发度最低。1、MySQL3种锁的特点如下：14.1.2 锁的概述2)页面锁：MySQL将锁定表中的某些行称

12、为页。被锁定的行只对锁定最初的线程是可行的。如果另外一个线程想要向这些行写数据，它必须等到锁被释放。不过，其他页的行仍然可以使用。其特点是：开销和加锁时间界于表级锁和行级锁之间；会出现死锁；锁定力度界于表级锁和行级锁之间，并发度一般。1、MySQL3种锁的特点如下：3)行级锁：行级锁比表级锁或页面锁对锁定过程提供了更精细的控制。在这种情况下，只有线程使用的行是被锁定的。表中的其他行对于其他线程都是可用的。在多用户的环境中，行级锁降低了线程间的冲突，可以使多个用户同时从一个相同表读数据甚至写数据。其特点是：开销大，加锁慢；会出现死锁；锁定力度最小，发生锁冲突的概率最低，并发度也最高。14.2.3

13、 MyISAM表的表级锁MyISAM在执行查询语句（select）前，会自动给涉及的所有表加读锁，在执行更新操作（update、delete、insert等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预。对MyISAM表进行操作，会有以下情况：（1）对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作。（2）对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。14.2.3 MyISAM表的表级锁1、查询表级锁争用情况show statu

14、s liketable% 2、MySQL表级锁的锁模式表共享读锁（table read lock）和表独占写锁（tablewrite i.ock）。3、表级锁的加锁方法在变化的过程中阻止其他用户访问正在变化的表（通过锁定表），只在变化完成后才允许访问14.2.3 MyISAM表的表级锁MySQL提供了LOCK TABLES语句来锁定当前线程lock tables tbl_nameas alias readlocal| low_priority write , tbl_name as alias readlocal| low_priority write . unlock tables 说明：表

15、锁定支持以下类型的锁定。read：读锁定，确保用户可以读取表，但是不能修改表。加上local后允许表锁定后用户可以进行非冲突的insert语句，只适用于MyISAM类型的表。write：写锁定，只有锁定该表的用户可以修改表，其他用户无法访问该表。加上low_priority后允许其他用户读取表，但是不能修改它。14.2.3 MyISAM表的表级锁在对一个事务表使用表锁定的时候需要注意以下几点：(1)在锁定表时会隐式地提交所有事务，在开始一个事务时，如start transaction ，会隐式解开所有表锁定。(2)在事务表中，系统变量autocommit值必须设为0。否则，MySQL会在调用l

16、ock tables之后立刻释放表锁定，并且很容易形成死锁。14.2.4 InnoDB表的行级锁InnoDB与MyISAM的最大不同有两点：一是支持事务（transaction ）；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引人也带来了一些新问题。show status like innoDB_rowiock%; 1)获取InnoDB行锁争用情况可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争用情况。如果发现innoDB_row_lock_waits和innoDB_row_lock_time_avg的值比较高，则说明锁争用比较严重。14.2.4

17、InnoDB表的行级锁2)InnoDB的行级锁的锁模式行锁:共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。排他锁(X)：允许获得排他事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁(Intention Locks)，这两种意向锁都是表锁。意向共享锁(IS)：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。意向排他锁(IX)：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。14.2.4 InnoDB表的行级锁意

18、向锁是InnoDB自动加的，不需用户干预。对于update、delete和insert语句，InnoDB会自动给涉及数据集加排他锁(X)；对于普通select语句，InnoDB不会加任何锁，共享锁(S): select * from table_name where lock in share mode。 排他锁(X): select * from table_name where for update。 3)InnoDB行级锁的加锁方法InnoDB行锁是通过给索引上的索引项加锁来实现的.14.2.5 死锁如果事务T1封锁了数据R1，T2封锁了数据R2，然后T1又请求封锁R2，因T2已封锁了R2，于是T1等待T2释放R2上的锁。接着T2又申请封锁R1，因T1己封锁了R1，T2也只能等待T1释放R1上的锁。这样就出现了T1在等待T2，而T2又在等待T1的局面，T1和T2两个事务永远不能结束，形成死锁。几种避免死锁的常用方法：1）在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访表，这样可以大大降低产生死锁的机会：2）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的