Mysql事务

一：mysql事务

1.需求

　　在示例数据库(yiibaidb)中添加新的销售订单的示例。

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

　　如果由于数据库故障而导致上述一个或多个步骤失败，那么数据会发生什么？ 如果将订单项添加到orderdetails表中的步骤失败，系统中将会有空的销售订单(只有订单号，不知道这个订单卖了什么)。

2.介绍

　　要启动事务，请使用START TRANSACTION语句。要撤消MySQL语句执行，请使用ROLLBACK语句。

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

　　　

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

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

　　

3.需求的步骤

• 使用START TRANSACTION语句启动事务。
• 从orders表中获取最新的销售订单编号，并使用下一个销售订单编号作为新的销售订单编号。
• 在指定orders表中插入新的销售订单。
• 将新的销售订单项目插入orderdetails表中。
• 使用COMMIT语句提交更改。
• 从orders表和orderdetails表中获取数据以确认更改。

4.sql

-- 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;

5.效果

　　

二：表锁定

1.介绍

　　使用MySQL锁来协调会话之间的表访问。

　　MySQL允许客户端会话明确获取表锁，以防止其他会话在特定时间段内访问表。客户端会话只能为自己获取或释放表锁。它不能获取或释放其他会话的表锁。

2.准备数据

　　

3.lock与unlock语法

　　lock：

　　

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

　　unlock：

　　

　　这个是要释放表的锁。

4.read类型

　　功能：　　

• 同时可以通过多个会话获取表的READ锁。此外，其他会话可以从表中读取数据，而无需获取锁定。
• 持有READ锁的会话只能从表中读取数据，但不能写入。此外，其他会话在释放READ锁之前无法将数据写入表中。来自另一个会话的写操作将被放入等待状态，直到释放READ锁。
• 如果会话正常或异常终止，MySQL将会隐式释放所有锁。这也与WRITE锁相关。

　　示例：

　　A：获取connection_id

　　　　

　　B：插入一行数据

　　　　INSERT INTO tbl(col) VALUES(10);

　　C：获取锁，并添加数据

　　　　LOCK TABLE tbl READ;

　　　　INSERT INTO tbl(col) VALUES(11);

　　　　会报错，因为这个时候不可以添加数据。

　　　　

　　D：打开另一个会话，查看Id

　　　　

　　E：插入数据

　　　　

　　F：查看详细 信息

　　　　

　　G：返回第一个会话，释放read锁

　　　　

5.write类型

　　功能：

• 只有拥有表锁定的会话才能从表读取和写入数据。
• 在释放WRITE锁之前，其他会话不能从表中读写。

 　　示例：

　　A：获取锁，并插入数据

　　

　　这时，去其他会话进行查询数据：

　　　　不能读写。

　　

　　B：回到会话一释放锁

　　　　UNLOCK TABLES;

　　C：进入会话二进行操作

　　　　这时可以进行读写操作，刚才被锁住的查询可以继续执行。