SQL中的事务——SQL读书笔记

 

《SQL学习指南》中的第12章

 

一.目录

 

•     背景
•     锁
• •      背景
  •      锁的概念
  •      锁的粒度
•      事务
• •      事务的概念
  •      启动事务
  •      结束事务
  •      事务的保存点

  

二.背景

          多个SQL语句同时执行的必要性和所需的基础设施

三.锁    

 

     1.背景

     

      数据库管理系统不但允许单个用户查询或者修改数据，而且可以多人同时操作数据库。如果多人进行查询，比如正常工作时      间中的数据仓库这种情况，那么数据库服务器只有很少的问题需要处理。然而，如果一些用户正在添加或者修改数据，那么服务器就必须做更多的簿记

    

     例如. 正在生成一个报表，显示本行开立对的所有支票账户的可用余额。但是，在生成报表的同时，发生了下面的活动：

          1）一名收付员正在处理客户的存款;

          2）一位客户正在前厅的ATM机上取钱

          3）银行的月结系统正在向账户支付利息

    

     因此，当生成报表时，还有多用户正在修改潜在的数据，报表上到底应该显示怎么的数据，下面开始解决这个问题

    

     2.锁的概念：

    

     锁是数据库服务器用来控制数据资源被并行使用的一种机制。当数据库的一些内容被锁定时，任何打算修改（或者可能是读取）这个数据的用户必须等到锁被释放。

         

       1）锁策略

• • •   数据库的写操作必须向服务器申请并获得写锁才能修改数据，而读操作必须申请和获得读锁才能查询数据。（数据库服务器对于表，页，行三者选一个只分配写锁，此时进行写操作，拒绝读操作直至写锁释放。可以多用户同时进行读操作）
    •   数据库的写操作必须向服务器申请并获得写锁才能修改数据，而读操作不需要任何类型的锁就可以查询数据。（同时，服务器要保证从查询开始到结束读操作，必须保证数据视图不改变，即使其他用户修改，这个方法称为版本控制）

         

       2）上面两种锁策略的缺点

                 对于第一种，有较多的并行读请求和写请求时等待时间过长

                 对于第二种，如果在修改数据时存在长期运行的查询--->这时候对于数据来说不是实时的 

       3）各种数据库使用的锁策略

          Microsoft SQL Server采取的是第一种方法，

          Oracle数据库使用的是第二种方法，

          MySQL则两种方法都包括（取决于使用者对存储引擎的选择）

 

     3.锁的粒度：

 

• • •  表锁：阻止多用户同时修改同一个的数据
    •  页锁：阻止多用户同时修改某表中同一页(一页通常是一段2~16KB的内存空间)的数据。
    •  行锁：阻止用户同时修改某表中同一行的数据

        

         1）对于不同粒度的锁也存在相应的优缺点：

            表锁需要较少的簿记就可以锁定表，但用户增多时它会迅速产生不可接受的等待时间。

            行锁允许多人修改同一个表的不同行，但行锁需要更多的簿记

         2）各种数据库所支持锁的粒度

            Microsof SQL Server使用表锁，页锁和行锁，在某种情况下，SQL Server会将锁从行锁升级至页锁，再从页锁升级至表锁

            Oracle只有行锁，从不升级锁

           MySQL采用表锁，页锁或行锁（取决于存储引擎的选择）

     

     对于上面的例子中，报表的生成，如果服务器使用版本控制方法，则报表上的数据反映的是报表开始生成时的数据库状态，如果服务器使用读锁和写锁，则报表上的数据反映的是服务器为报表程序创建读锁时的数据库状态。

 

四.事务   

 

     1.事务的概念

     事务是一种将多条SQL语句聚集到一起,并且能够实现要么所有语句都执行，要么一个都不执行（这个属性称为原子性）

     假如从储蓄账户划出$500到支票账户，如果钱已从储蓄账户成功支取却没有成功存入支票账户？

     为了避免上述方式的错误，我们的处理方式应该这样，首先启动一个事务，然后发起SQL语句将储蓄账户转到支票账户，如果成功，则发出commit命令结束事务；否则，就发出rollback命令撤销服务器从事务开始时的所有变化。

     上述方式的伪代码：

             

     START TRANSACTION; 

      /*从id为9988的账户转出$500*/

     UPDATE account SET avail_balance = avail_balance -500

         WHERE account_id = 9988

         AND avail_balance > 500;

        IF </*假设转出执行成功*/> THEN

      /* 将上面转出$500加入要加入的账户中*/

                UPDATE account SET avail_balance = avail_balance + 500

                WHERE account_id = 9989;

                IF </*假设转入执行成功*/> THEN

                    COMMIT;

                ELSE

                    ROLLBACK;

                END IF

        ELSE

            ROLLBACK;

        END if;

 

  上面的事务执行的过程中，会出现两种极端的情况：1）程序设法完成两个update语句后，还没有执行commit或rollback命令，服务器突然宕机了，这时候服务器的解决方式：数据库在重新上线前必须执行的任务：查找宕机前正在运行但未完成的事务将其回滚；2)如果程序完成了事务，并发出了commit命令，但是被修改的数据还位于内存，但没有被存入磁盘中，此时服务器宕机了，那么服务器解决方式是：服务器重启时数据库服务器必须重新应用事务的变化 

 

     2.启动事务

 

       1）启动事务的方法：

          1.一个活跃事务总是和数据库会话相联系，所以没有必要，也没有什么方法能够显式地启动一个事务。当前事务结束时，服务器自动为会话启动一个新的事务。(自动创建事务，必须手动提交事务和回滚)

          2.如果不显式地启动一个会话，单个的SQL语句会被独立于其他语句自动提交。启动一个事务之前需先提交一个命令(手动开启事务，自动提交事务)

 

       2）各种数据库启动事务的方式

 

            Oracle数据库使用的是第一种方法，优点是即使至提交单个SQL指令的情况下，也可以回滚所有变化

 

            MySQL和SQL Server 使用的是第二种方法，必须显示的来启动一个事务，MySQL使用的是 start transaction ; SQL Server 使用的是 begin transaction;对于这两个服务器来说，一直处在自动提交模式，这表示单个语句会被服务器自动提交。

 

            同时，MySQL和SQL Server都允许读者为单个会话关闭自动提交模式，这时候服务器便会像Oracle一样自动创建会话。 SQL Server使用 SET IMPLICIT_TRANSACTIONS ON 命令，MySQL使用SET AUTOCOMMIT= 0命令关闭自动提交模式。（注意，一旦关闭了自动提交模式，所有的SQL命令都会发生在同一个事务的范围，并且必须显式的对事务进行提交或者回滚）

     

      3.结束事务

 

        1）结束事务的触发场景：

 

          1.commit命令：该指令使服务器将变化标记为永久性的(将内存中的数据变化，写入到硬盘中)进而释放事务中使用的任何资源（包括锁）。

          2.rollback命令：该指令使服务器将数据返回处理前的状态(s事务启动时)，任何会话中的资源都会被释放。

         --------------------------------------------------------------------------------------

          3.服务器宕机，在这种情况下服务器重启时将会被自动回滚

          4.提交一个SQL模式语句，比如alter table ,这会引起一个事务的提交

          5.提交一个start transaction,将会引起起一个事务的提交。

          6.如果服务器检测到一个死锁，且死锁的原因是由当前事务引起的，那么服务器就会结束当前事务，将事务进行回滚，同时释放错误消息

      

      4.事务保存点

 

        1)事务保存点概念：

          

          有时候会遇到一些问题需要回滚，但是并不需要所有做过的工作。这时候事务保存点就会起到它的作用，可以在在事务内创建一个或者多个保存点，这样就可以利用它们回滚到事务的特殊位置而不必回滚到事务启动时的初始状态。

        

         1.创建事务保存点：事务的保存点需要拥有一个名字，这样只要保证名字不同，就可以在单个事务中创建多个保存点，如

          SAVEPOIONT my_firstpoint;

 

         2.回滚到特定的事务保存点:

          如

          ROLLBACK TO SAVEPOINT my_firstpoint

         3.如何使用保存点的完整例子

        

START TRANSACTION;

SET date_retired = CURRENT_TIMESTAMP()

WHERE product_cd = 'XYZ';

 

SAVEPOINT before_close_accounts

 

UPDATE account

SET status = 'CLOSED', close_date = CURRENT_TIMESTAMP(),

last_activity_date = CURRENT_TIMESTAMP()

WHERE product_cd = 'XYZ';

 

ROLLBACK TO SAVEPOINT before_close_accounts;

COMMIT;

          

        注意：

•               创建保存点时，除了名字，什么都没有保存。但最终为了保证事务持久化，读者必须发出一个commit命令；
•               如果使用一个没有保存点的rollback命令，那么事务中所有的保存点都会被忽略，并且撤销整个事务。
•               对于SQL Server 使用 save transaction 保存点名字 创建保存点，使用 rollback transaction 保存点名字  回滚一个保存点

                             

                  

转载于:https://www.cnblogs.com/yona0826/p/6001874.html