《数据库原理及应用教程-MySQL8.0》课件 第10章 事务与并发控制

数据库原理与应用教程-MySQL8.0第10章

事务与并发控制第10章

事务与并发控制关系型数据库有四个显著的特征，即安全性、完整性、并发性和监测性。数据库的安全性就是要保证数据库中数据的安全，防止未授权用户随意修改数据库中的数据，确保数据的安全。完整性是数据库的一个重要特征，也是保证数据库中的数据切实有效、防止错误、实现商业规则的一种重要机制。对任何系统都可以这样说，没有监测，就没有优化。只有通过对数据库进行全面的性能监测，也才能发现影响系统性能的因素和瓶颈，才能针对瓶颈因素，采取切合实际的策略，解决问题，提高系统的性能。第10章

事务与并发控制为了充分利用数据库资源，发挥数据库共享资源的特点，应该允许多个用户并行地存取数据库。但这样就会产生多个用户程序并发存取同一数据的情况，若对并发操作不加控制就可能会存取和存储不正确的数据，破坏数据库的一致性，所以数据库管理系统必须提供并发控制机制。并发控制机制的好坏是衡量一个数据库管理系统性能的重要标志之一。MySQL以事务为单位通常使用锁来实现并发控制。当用户对数据库并发访问时，为了确保事务完整性和数据库一致性，需要使用锁定。这样，就可以保证任何时候都可以有多个正在运行的用户程序，但是所有用户程序都在彼此完全隔离的环境中运行。。10.1事务事务处理是数据库的主要工作，事务由一系列的数据操作组成，是数据库应用程序的基本逻辑单元，用来保证数据的一致性。事务和存储过程类似，由一系列SQL语句组成，是MySQL系统的执行单元。在数据库处理数据的时候，有一些操作是不可分割的整体，例如，用银行卡消费时首先要在账户扣除资金，然后再增加资金到商家账户。这些操作是不可分割的，不能只扣除不增加，也不能只增加不扣除。事务可以解决上面的问题，即把这些操作放在一个容器里，强制用户执行完所有的操作或者不执行任何一条语句。10.1.1事务的特性在MySQL中，事务必须具备四个特性：(1)原子性：事务必须是原子工作单元，对于其数据修改，要么全都执行，要么全都不执行。(2)一致性：一致性要求事务执行完成后，将数据库从一个一致状态转变到另一个一致状态。(3)隔离性：也称为独立性，是指并行事务的修改必须与其它并行事务的修改相互独立。(4)持久性：在事务完成提交之后，就对系统产生持久的影响，即事务的操作将写入数据库中，无论发生何种机器和系统故障都不应该对其有任何影响。10.1.2事务的类型1.自动提交事务如果没有通过任何语句设置事务一条SQL语句就是一个事务，即自动提交事务，语句执行完事务就结束。CREATE、ALTER、DROP、INSERT、UPDATE、DELETE、SELECT、FETCH、OPEN、GRANT、REVOKE等【例10-1】使用CREATETABLE创建一个表。CREATETABLEstudent(IdCHAR(10),NameCHAR(6),SexCHAR(1))这条语句本身就构成了一个事务。10.1.2事务的类型2.用户定义事务在实际应用中，大多数的事务处理采用了用户定义的事务来处理。STARTTRANSACTION或BEGINWORK语句来定义明确的用户定义事务的开始。COMMITTRANSACTION或COMMITWORK语句将全部完成的语句明确地提交到数据库中，表示事务操作成功。ROLLBACKTRANSACTION或ROLLBACKWORK语句将事务的操作全部回滚，即表示事务操作失败。10.1.3事务处理语句1．START或BEGIN语句语法格式为：STARTTRANSACTION|BEGINWORK;MySQL默认是自动提交模式，即如果没有显示开启事务，每一条SQL语句都会自动提交。如果想要将一组SQL语句作为一个事务，则必须先执行以上语句显示地开启一个事务。10.1.3事务处理语句2.COMMIT语句COMMIT语句用于提交一个事务，它标志一个显式开启的事务成功的结束。语法格式：COMMIT[WORK];3.ROLLBACK语句ROLLBACK语句将显式开启的事务回滚到事务的起点或事务内的某个保存点，它也标志一个事务的结束。其语法格式为：ROLLBACK[WORK][TOsavepoint_name];10.1.3事务处理语句4.SAVEPOINT语句SAVEPOINT语句用于在事务内设置保存点。语法格式：SAVEPOINTsavepoint_name;在事务内的某个位置建立一个保存点，使用户可以将事务回滚到该保存点的状态，而不回滚整个事务。一个事务中可以设置多个保存点，提交事务后保存点会被删除。如果事务被回滚到到某个保存点，在该保存点后创建的保存点也会消失。删除不需要的保存点，语句：RELEASESAVEPOINTsavepoint_name;10.1.3事务处理语句使用事务时应注意以下两点：(1)不是所有的SQL语句都能放在事务里，通常INSERT、UPDATE、DELETE、SELECT等数据操作语句可以放在事务里，创建、删除、修改数据库或表等不能放在事务里。(2)事务要尽量的小，而且一个事务占用的资源越少越好。10.1.3事务处理语句【例10-2】定义一个事务，将所有选修了C004号课程的学生的分数加5分，并提交该事务。STARTTRANSACTION;USEteaching;UPDATEscSETscore=score+5WHEREcno='C004';COMMIT;10.1.3事务处理语句【例10-3】定义一个事务，向teaching库的student表中插入一行数据，然后再删除该行。执行后，新插入的数据行并没有被删除。利用事务保存点来完成。STARTTRANSACTION;USEteaching;INSERTINTOstudent(sno,sname,ssex,sbirthday,major,grade)VALUES('2022010010','朱一虹','女','2003-5-6','电子信息','2022级');SAVEPOINTsavepoint1;DELETEFROMstudentWHEREsname='朱一虹';ROLLBACKTOsavepoint1;COMMIT;10.1.3事务处理语句【例10-4】创建一个存储过程，在其中定义一个转账事务，向bankcard数据库的Trecord表中插入一行某账号的转出记录数据，另一账号的转入记录数据。两个插入操作都会触发例9-11的Transactions触发器，修改相应的余额。USEbankcard;DELIMITER//CREATEPROCEDURErecord_tran()BEGINSTARTTRANSACTION;INSERTTrecord(TDate,AccNO,Expense,Income,OppAccNO,Place,Abstract)VALUES(CURDATE(),‘412542800335120***06’,500,NULL,‘436742800335120***05',NULL,'转账支出');10.1.3事务处理语句INSERTTrecord(TDate,AccNO,Expense,Income,OppAccNO,Place,Abstract)VALUES(CURDATE(),‘436742800335120***05’,NULL,500,‘412542800335120***06',NULL,'转账收入');COMMIT;END//调用存储过程：CALLrecord_tran();10.2并发控制并发控制指的是当多个用户同时更新行时，用于保护数据库完整性的各种技术，目的是保证一个用户的工作不会对另一个用户的工作产生不合理的影响。在某些情况下，这些措施保证了当用户和其他用户一起操作时，所得的结果和他单独操作时的结果是一样的。10.2.1并发带来的问题(1)丢失修改：指在一个事务读取一个数据时，另外一个事务也访问该同一数据。那么，在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务T1读取某表中数据A=20，事务T2也读取A=20，事务T1修改A=A-1，修改，事务T2也修改A=A-1；最终结果A=19,事务T1的修改被丢失。10.2.1并发带来的问题(2)脏读：指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。例如：事务T1读取某表中数据A=20，并修改A=A-1，写回数据库，事务T2读取A=19，事务T1回滚了前面的操作，事务T2也修改A=A-1；最终结果A=18,事务T2读取的就是“脏数据”。10.2.1并发带来的问题(3)不可重复读：指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。在一个事务内两次读到的数据不一样，就称为不可重复读。例如：事务T1读取某表中数据A=20、B=30，求C=A+B，C=50，事务T1继续往下执行；事务T2读取A=20，修改A=A*2，A=40；事务T1又一次读取数据A=40、B=30，求C=A+B，C=70；所以，在事务T1内两次读到的数据是不一样的，即不可重复读。10.2.1并发带来的问题(4)幻读：与不可重复读相似，是指当事务不是独立执行时发生的一种现象。例如，第一个事务对一个表中的全部数据行都进行了某种修改；同时，第二个事务向表中插入了一行数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好像出现了幻觉一样。当对某条记录执行插入或删除操作而该记录属于某个事务正在读取的行的范围时，会发生幻读问题。10.2.2设置事务的隔离级别为了防止数据库的并发操作导致的丢失修改、脏读、不可重复度和幻读等问题，SQL标准定义了4类隔离级别。（1）读取未提交数据（ReadUncommitted）在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也就是脏读。10.2.2设置事务的隔离级别（2）读取提交的数据（ReadCommitted）这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义，即一个事务只能看见已经提交事务所做的改变。这种隔离级别支持所谓的不可重复读，因为同一事务的其他实例在该实例处理其间可能会有新的提交结果，所以同一select可能返回不同结果。10.2.2设置事务的隔离级别（3）可重复读（RepeatableRead）这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读。（4）可串行化（Serializable）这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁，事务顺序执行，没有并行，完全杜绝幻读。在这个级别，可能导致大量的超时现象和锁竞争。该隔离级别主要用于InnoDB存储引擎的分布式事务。10.2.2设置事务的隔离级别MySQL的默认隔离级别为RepeatableRead，在应用程序具有更高或更低隔离级别的具体要求时才需要改动。这种要求并没有一个固定的标准，一般是基于应用程序的容错能力和开发者对于潜在数据报错的影响的经验判断。10.2.3锁简介锁是实现并发控制的主要方法，是防止其他事务访问指定的资源、实现并发控制的一种手段，是多个用户能够同时操纵同一个数据库中的数据而不发生数据不一致现象的重要保障。为了提高系统的性能、加快事务的处理速度、缩短事务的等待时间，应该使锁定的资源最小化。10.2.3锁简介数据库中的锁是一种软件机制，用来指示某个用户已经占用了某种资源，从而防止其他用户做出影响本用户的数据修改或导致数据库数据的不完整性和不一致性。所谓资源，主要指用户可以操作的数据行、数据表等。根据资源的不同，锁有多粒度的概念，也就是指可以锁定的资源的层次。MySQL中能够锁定的资源粒度主要包括：数据表级、页级和行级等等。其中MyISAM存储引擎采用表级锁；InnoDB存储引擎支持表级锁和行级锁，但默认情况下采用行级锁；DBD存储引擎采用页级锁，但也支持表级锁。10.2.3锁简介表级锁开销小加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低；行级锁开销大加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高；页级锁开销和加锁时间界于表锁和行锁之间，会出现死锁，锁定粒度界于表锁和行锁之间，并发度一般。仅从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理系统。10.2.4锁的分类数据库引擎使用不同类型的锁锁定资源，这些锁确定了并发事务访问资源的方式。MyISAM的表锁在执行查询操作（select）前，会自动给涉及的所有表加读锁；在执行更新操作（insert、update、delete等）前，会自动给涉及的所有表加写锁。(1)读锁读锁又叫共享锁(SharedLock，S锁)，该锁允许并发事务读取（SELECT）一个资源。资源上存在S锁时，任何其它事务都不能修改数据。10.2.4锁的分类(2)写锁写锁也叫排它锁（ExclusiveLock，X锁），该锁可以防止并发事务对资源进行访问，其它事务不能读取或修改X锁锁定的数据。即X锁锁定的资源只允许进行锁定操作的程序使用，其它任何对它的操作均不会被接受。执行数据更新命令即INSERT、UPDATE或DELETE命令时MySQL会自动使用X锁，但当对象上有其它锁存在时无法对其加X锁。MyISAM表的读操作与写操作之间、以及写操作之间是串行的。10.2.4锁的分类MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行更新操作前，会自动给涉及的表加写锁，这个过程并不需要用户干预。当写进程和读进程同时请求同一个MyISAM表的写锁和读锁时，写进程会优先获得锁。即使读请求先于写请求到达锁等待队列，写锁也会插到读锁请求之前。因为MySQL认为写请求一般比读请求更重要，所以MyISAM表不太适合有大量更新操作和查询操作的应用，因为大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。10.2.4锁的分类2.InnoDB的锁InnoDB的行锁有两种：共享锁(S)和排他锁(X)。为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁：意向共享锁(IS锁)和意向排他锁(IX锁)，这两种意向锁（IntentLock）都是表锁。一个事务在给数据行加锁之前必须先取得对应表对应的意向锁。10.2.4锁的分类（1）IS锁IS锁表示某事务有读取低层次资源的意向，把S锁放在这些低层次的单个资源上。也就是说，如果对一个数据对象加IS锁，表示它的后裔资源拟（意向）加S锁。例如，要对某个元组加S锁，则要首先对包含该元组的表加IS锁。（2）IX锁IX锁表示某事务有修改低层次资源的意向，把X锁放在这些低层次的单个资源上。也就是说，如果对一个数据对象加IX锁，表示它的后裔资源拟（意向）加X锁。例如，要对某个元组加X锁，则要首先对包含该元组的表加IX锁。10.2.4锁的分类对于InnoDB表，在绝大部分情况下都应该使用行级锁，但在个别特殊事务中，也可以考虑使用表级