sql基础－事务

sql基础

事务：为了保持逻辑数据的一致性和恢复性，即一个逻辑操作内，所有的操作单元要么做，要么全部不做。

锁：当多个用户同时访问同一个数据库资源时，对访问的先后秩序进行管理和安排的一种机制，是实现事务的技术，保证数据库的安全读写。

死锁：上述锁机制中，因为不同事务在同一时间抢占相同的资源而导致的事务挂起等待，导致数据库在死锁期间造成的极大负荷和资源浪费。

 

和其他基础sql tutorial讲解sql事务一样，银行取钱中，取出100过程中，如果成功则账户减少100；如果中途机器故障则返回不扣。事务就是：要么全部执行，要不一点不执行回归原始状态。

 

1.特性：

原子性atomicity：一个事物内的所有操作是一个整体不可在分割，要么全部执行要么全部不执行。

一致性consistency：事务结束后，所有的数据状态是正确的。

隔离型isolation：不同的事务之间互不干扰，处理的都是任何其他事务处理前或者后的数据状态。

持久性persistence：事务提交后，数据恒久的不可回滚的。

 

2.提交回滚：

在不同环境下，可能不同数据库在sql语句执行后，需要显示的commit提交，rollback回滚。

 

3.锁：

我们想象下，在多个用户都是用事务同时访问相同资源时候，会造成以下问题：

 1）丢失更新：多个用户同时对一个数据资源进行更新操作，必定会产生覆盖的情况，最后自由一个结果，可能造成数据读写异常

 2）不可重复读：用户A在一个事务中，前后多次读取同一条数据，但是在此时刻用户B对该数据更新，造成用户A前后读取数据不一致；

 3）脏读：第一个事务正在读取第二个事务正在处理的数据，如果第二个事务没有完成所有的更新，第一个事务继续对这个结果集查询的数据将一半是更新过的一般还没有更新，这样的结果逻辑错误。

 4）幻读：用户A在一个事务中，前后多次读取同一条数据，但是在此时刻用户B对该数据库增加或者删除，造成用户A前后读取数据丢失或者增加；

 

 5）锁就是为了解决以上4中issue而产生的数据库事务处理机制，它对在一个事务所保护的所有一系列操作的集合进行一个包装，其他事务不能插足，即锁定（锁）。

  <1>.共享锁（s 表级）：称之为只读锁，可以并发读取数据，但是不能修改数据。就是当数据资源上有共享锁时候，所有事务都不能对这个资源进行更新，只能读取。直到数据读取完成，共享锁释放。

  <2>.排它锁（x 行级）：称之为独占锁、写锁。如果对数据资源进行增删改查操作，不再允许任何其他事务进行任何操作， 直到锁释放。

  <3>.更新锁（U）：防止死锁的模式。两个事务对一个数据资源进行先读取在更新的操作，使用共享锁或者排它锁会出现死锁现象，使用更新锁能避免。资源的更新锁依次只能分配给一个事务，任何对资源进行修改，则编程排它锁，否则共享锁。

  <4>.意向锁：在层次架构的底层资源上（行、表）获取共享锁、排它锁、更新锁。例如表级防止了意向共享锁就表示事务要对表的页或者行使用共享锁。在表的一行上放置意向锁，可以放置其他事务获取其他不兼容的锁。它可提高性能，因为数据库引擎不需要检测资源的每一行每一列，就能判断是否可以获取到该资源的兼容锁。意向共享锁，意向排它锁，意向排他共享锁。

  <5>.架构锁：放置修改表结构时，并发访问的锁。

  <6>.大容量更新锁：允许多个县策划那个将大量的数据并发迁移到同一个数据表中，在加载时候不允许其他线程访问该表。

 

以上六种锁中，1.2.3是使用最频繁的。一般的数据库事务读操作使用共享锁，而写操作使用排它锁，在视具体业务而定。

 

4.死锁

死锁，就是事务将资源锁定而不释放，导致其他的事务无法使用该资源而出现的系统等待拥挤或者超时崩溃。

出现的场景：

 step1.事务A：先更新表a－－延时1s－－更新表b

 step2.同时有事务B：先更新表b－－延时1s－－更新表a

 step3.在1s之内同时执行事务A、B，出现死锁。

 因为在1s的延时过程中，事务给a更新时加排它锁1，在更新b是请求b排它锁2，而b已经占用排它锁2，同时更新a也要请求a的排它锁1，这样相互等待而两个事务又不愿意释放各自锁的情况下，永远在等待这个死循环。

 当然，数据库没有出现无限等待的情况，因为数据库引擎会定时检测一旦有死锁，将会按照一定规则，牺牲一个事务的代价释放所占用的锁而解决。被牺牲的事务回滚。

 死锁的牺牲规则，认为可设置优先级，优先级低的先牺牲。相同优先级的随机。

 

死锁是一种大量消耗时间资源的罪魁祸首，在大型系统应用中，虽然不可比避免，但是应当尽量优化sql，减少死锁：

  <1>更新锁不悔出现死锁。

  <2>在一个事务中，尽量不处理过多的复杂逻辑。

  <3>设置事务自动提交。

  <4>减少数据库并发访问。

  <5>使用分区表，视图。将数据放置在不同的磁盘或者文件组中，分散数据资源。

  <6>设计表时，优化逻辑，不要太复杂。

  <7>事务隔离级别尽量低。

在实际生产中，不会被动等待数据库本身的死锁检查机制来处理死锁，可以主动设置超时时间，这样不仅处理死锁，还处理了系统因为其他原因导致的等待时间超时而主动撤销事务，释放资源。并且在程序中手动这时回滚逻辑，保证数据的一致性。

 

5.事务隔离级别

一个事物与其他事务进行的资源、数据的更改相隔离的程度。隔离级别是从允许并发的副作用来描述数据库事务。

  1）read uncommittde：未提交读，最低隔离级别，A事务可以读取到B事务正在更改还没有提交的数据。对应锁的脏读。

  2）read committed：已提交读，它是sql默认的隔离级别，A事务只能读到B事务提交前或者提交后的数据。不会出现脏读，但会出现幻读，不可重复读。

  3）repeatable read：可重复读，不能读取到事务正在处理的数据，也不能修改事务正在处理的数据，不会出现脏读，不可重复读，但是会幻读。

  4）serialization：序列化，最高的事务隔离级别，一个事物的会将整张数据表锁定导致只能看到事务处理前的数据。不会出现脏读、不可重复读、幻读。