sql学习指南-锁和事务
锁：
背景：数据库管理系统不但允许单个操作，还允许多个人同时操作，那么以银行生成报表同时为例，一名职员正在处理用户的存款，另一个客户正在ATM上操作账户金额，那么现在银行生成的报表中该显示什么时候的数据呢？
答案在一定程度上取决于系统如何处理锁
锁的定义
是数据库服务器用来控制数据资源被并行使用的一种机制。
- 也就是说数据库的内容被锁定时，任何打算读取或者修改这个数据的用户必须等到锁的释放。
- 读锁：也叫共享锁，在同一时段内，多个用户可以读取同一个资源，读取的过程中数据不会变化
- 写锁：也叫排它锁，在任何时候只能又一个用户写入资源，当进行写锁的时候，会阻塞其他的读锁或者写锁的操作。
锁的方案
1、数据库的写操作必须向服务器申请并获得写锁才能够修改数据，而读操作必须申请和获得读锁才可以查询数据，多个用户可以同时读取数据，而一个表或者其他部分一次只能分配一个写锁，并且拒绝读请求直至写锁释放。
2、数据库的写操作必须想服务器申请并获得写锁才能修改数据，而读操作不需要任何类型的锁就可以查询数据。另一方方面，服务器要保证从查询开始到结束读的操作看到的一个一致的数据视图，即使其他用户修改数据看上去也要相同，这个方法称之为控制版本。
以上两种方案各有利弊，第一种方案，如果有过度的读请求和写请求并行的时候，等待时间会比较长。第二种方案如果修改数据时存在长期的运行查询，那么也是有问题的。
另外，mysql应用了以上两种方法，取决于读者对存储引擎的选择
锁的粒度
决定如何锁定一个资源也可以采用一些不同的策略，服务器可能在3个不同级别之一应用锁，称为锁的粒度：
表锁(开销最小的锁策略)：组织多用户同时修改同一个表的数据。页锁：组织多用户同时修改某表中的同一页数据（一页通常是一段2-16k的内存空间）
行锁(开销最大的锁策略)：组织多用户同时修改某表中的同一行数据。
- 以上三种锁各有利弊。如果使用表锁，表锁需要较少的开销可以锁定表，但用户增多时它会产生不可接受的等待时间，如果使用行锁，那么需要比较多的开销，但是只要各个用户在意不同行的数据，那么这样就允许多人同时修改同一张表。当Mysql采用了表锁，页锁，行锁，取决于不同的存取引擎。
- 了解以上基础内容后，那么此时生成银行报表问题就可以得出两个点，是要反映服务器为报表程序创建读锁时候的数据库状态（服务器使用读锁和写锁。），还是报表上的数据要么反映报表生成时的数据库状态（版本控制）。
事务
背景：当从账户中划走相应金额的时候，由于sql语句执行出现失败，那么对方账户没有收到相应金额的钱
所以为了避免这种悲伤的情况，此刻转账程序就要先申请开启一个事务，然后发起sql语句开始转账，当所有sql语句都执行成功，此时会发出commit命令结束事务，否则，就会发出rollback的命令撤销服务器上自事务开始时产生的所有变化。
事务的定义：是一组有逻辑的操作集合构成的工作单元。在银行转账例子中的作用主要是要么能全部执行成功，要么就一个都不执行。
事务的四个特性：ACID（原子性，一致性，隔离性，持久性）
- 原子性：事务中的所有操作作为一个整体，像原子一样不可分割，要么全都成功，要么全都失败。（也是事务的核心特性！）基于undo log。
- 一致性：事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态。（也可以这样理解，事务执行结束后，数据库的完整性约束并没有被破坏，事务执行的前后都是合法的数据状态。）（不光数据库需要摆正，也是应用层面配合保证）
- 隔离性：并发执行的事务并不会相互影响，其对数据库的影响和他们串行执行时一样。比如多个用户同时往一个账户转账，最后的账户结果应该和他们按照先后次序转账的结果一致。基于undo logo和一些锁的机制。
- 持久性：事务一旦提交，其对数据库的更新就是持久性的，任何事务或者系统故障都不会导致数据丢失。基于redo log。
启动事务
- 一个活跃事务总是和数据库会话相关，所以并不需要显示的开启一个事务，当事务结束时服务器会自动为会话启动一个新的事务。
- 如果不显示的启动一个会话，单个sql语句会被独立于其他语句自动提交，启动一个事务之前需要先提交一个命令。
并且，Mysql允许用户手动关闭自动提交模式：set autocommit=0，如果脱离了自动提交，那么所有的sql语句都发生在一个事务的范围里，并且必须显式的对事务进行提交或者回滚。
在Mysql中存在一些特殊的命令，如果在事务中执行了这些命令，例如：create tb、drop tb、alter tb、locks tb就会立刻强制执行commit提交事务。一般的select ，insert，update，delete都不会强制提交事务。
或者又重新start transaction命令，这时会引起前一个事务提交。
另外，服务器宕机，那么服务器重启时事务会自动回滚。当服务器检测到一个死锁的时候，并且确定当前事务就是死锁的原因，那么服务器就会提前结束当前事务，并且事务回滚，返回错误信息
结束事务
事务启动后，不管使用start transaction命令显式启动还是有数据库服务器隐式启动，为了持久化数据变化都必须显式的结束事务，也就是用commit命令，使服务器解标记这些变化为永久性的，进而释放事务中使用的任何资源（页锁和行锁之类的）
这里简单理解下死锁：也就是不同事务等待同一资源，而这个资源被另一个事务所拥有。例如：事务A更新了tb1，等待tb2的写锁继续操作。而同时，事务B更新了tb2，等待tb1的写锁继续操作，这时就相当于无限期的等待着对方拥有的资源，这时就会发生死锁。这种情况下，必须选择一个事务进行回滚，另一个执行成功。
事务的保存点
问题背景：在某些情况下，事务在遇到问题需要回滚，但是并不想撤销所有做过的工作，此时就可以在事务内创建一个或者多个保存点，这样就可以利用他们回滚到事务的特定位置而不必一路回滚到事务启动状态。
提到这个问题就不得不说，mysql中的存储引擎
选择存储引擎
Mysql中的存储引擎（show engines）查看默认使用了哪个引擎（show variables like），查看当前表使用了哪个存储引擎（show create table table_name/show table status like 'table_name'后者格式清晰）
- MyISAM：一种采用表级锁定的非事务引擎
- innoDB：一种使用行级锁定的事务引擎
- MEMORY：一种内存表使用的非事务引擎
- Archive：一种用于存储大量为索引数据的专用引擎，主要用来存档
- BDB：一种采用页级锁定的事务引擎
- Merge：一种使用多个相同的MyISAM看起来像是一个单表的专用引擎
- Maria：6.0.6版本中MyISAM的替代品，添加了充分的恢复功能
- Falcon：6.0.4版本中引入的采用行级锁定的高性能事务引擎
在创建一个表的时候，可以显式的指定该表使用什么样的存储引擎。alter table table_name transaction engine = InnoDB
- MyISAM：访问速度快，如果以select和insert应用为主的基本都可以选，web和数据仓库中广泛应用。但对事务的完整性没什么特别的要求。纯读或者纯写等，或者以读为主的业务，不需要支持事务的业务场景均可。
不支持事务和外键。缓存索引文件，不缓存数据文件，表锁，读写互相阻塞，支持全文索引，是mysql5.5以前的默认存储引擎。
- InnoDB：是mysql5.5.8开始的默认存储引擎。行锁，支持事务，支持外键，崩溃恢复能力的事务安全，数据更新频繁的场景，数据一致性要求高的场景转账，报表或者充值等业务，比MyISAM引擎需要更多的资源，所以速度也没有它快。
硬件设备内存要大，InnoDB的缓存能力较高减少磁盘IO，数据一致性要求较高的业务，数据频繁更新（社交平台），需要事务支持的业务，高并发有很好的的适应能力。
InnoDB中，自动增长列必须是索引，如果是组合索引，必须是组合索引的第一列。
MyISAM中自动增长列可是组合索引中的其他列
InnoDB中的某个表被其它的的表创建了外键参照，那么该表的对应索引和主键禁止被删除。
