MySQL 事务

• 一、MySQL 事务

• 要了解MySQL中的事务是什么，我们先来看看在示例数据库(yiibaidb)中添加新的销售订单的示例。添加销售订单的步骤如下所述：

  • 从orders表中查询最新的销售订单编号，并使用下一个销售订单编号作为新的销售订单编号。
  • 在指定客户的orders表中插入新的销售订单。
  • 将新的销售订单项目插入orderdetails表中。
  • 从orders表和orderdetails中获取数据以确认更改。

  现在想象如果由于数据库故障而导致上述一个或多个步骤失败，那么数据会发生什么？ 如果将订单项添加到orderdetails表中的步骤失败，系统中将会有空的销售订单(只有订单号，不知道这个订单卖了什么)。数据可能不完整，那么必须花费的精力来解决这个问题。

  如何解决这个问题？ 这就是为什么事务来处理。 MySQL事务使您能够执行一组MySQL操作，以确保数据库从不包含部分操作的结果。 在一组操作中，如果其中一个失败，则会恢复回滚数据库。 如果没有发生错误，则将整个语句集合提交到数据库。

• 要启动事务，请使用start transaction语句。要撤消MySQL语句执行，请使用rollback语句。

  请注意，有一些SQL语句，主要是数据定义语句，不能在事务中使用以下语句：

• CREATE / ALTER / DROP DATABASE
  CREATE /ALTER / DROP / RENAME / TRUNCATE TABLE
  CREATE / DROP INDEX
  CREATE / DROP EVENT
  CREATE / DROP FUNCTION
  CREATE / DROP PROCEDURE
  …

  要将更改写入事务中的数据库，请使用COMMIT语句。要注意的是，默认情况下，MySQL自动提交对数据库的更改。

  要强制MySQL不会自动提交更改，请使用以下语句：

• SET autocommit = 0

  MySQL 事务示例：

• 要使用MySQL事务，首先必须将MySQL语句分解成逻辑部分，并确定何时应该提交或回滚数据。

  下面来看一下使用MySQL事务在上面的示例数据库中添加新的销售订单并添加事务处理步骤的例子：

• 1、使用 start transaction 语句启动事务。
• 2、从orders 表中获取最新的销售订单编号，并使用下一个销售订单编号作为新的销售订单编号。
• 3、将新的销售订单项目插入orderdetails 表中。
• 4、使用commit 语句提交更改。
• 5、从orders 表和orderdetails 表中获取 数据以确认更改。

• 以下是执行上述步骤的脚本：

• -- start a new transaction
  start transaction;
  
  -- get latest order number
  select @orderNumber := max(orderNUmber) 
  from orders;
  -- set new order number
  set @orderNumber = @orderNumber  + 1;
  
  -- insert a new order for customer 145
  insert into orders(orderNumber,
                     orderDate,
                     requiredDate,
                     shippedDate,
                     status,
                     customerNumber)
  values(@orderNumber,
         now(),
         date_add(now(), INTERVAL 5 DAY),
         date_add(now(), INTERVAL 2 DAY),
         'In Process',
          145);
  -- insert 2 order line items
  insert into orderdetails(orderNumber,
                           productCode,
                           quantityOrdered,
                           priceEach,
                           orderLineNumber)
  values(@orderNumber,'S18_1749', 30, '136', 1),
        (@orderNumber,'S18_2248', 50, '55.09', 2); 
  -- commit changes    
  commit;       
  
  -- get the new inserted order
  select * from orders a 
  inner join orderdetails b on a.ordernumber = b.ordernumber
  where a.ordernumber = @ordernumber;

• 二、MySQL表锁定
• lock 和unlock tables 语法
• 获取表的锁的简单形式如下：

• LOCK TABLES table_name [READ | WRITE]

  可将表的名称放在LOCK TABLES关键字后面，后跟一个锁类型。 MySQL提供两种锁类型：READ和WRITE。 我们将在下一节详细介绍这两种锁类型。

• UNLOCK TABLES;

  表锁定为read 

• 表的READ锁具有以下功能：

• 同时可以通过多个会话获取表的READ锁。此外，其他会话可以从表中读取数据，而无需获取锁定。

• 持有READ锁的会话只能从表中读取数据，但不能写入。此外，其他会话在释放READ锁之前无法将数据写入表中。来自另一个会话的写操作将被放入等待状态，直到释放READ锁。

• 如果会话正常或异常终止，MySQL将会隐式释放所有锁。这也与WRITE锁相关。