MySQL事务

MySQL事务的介绍

1) 事务概念

一组mysql语句，要么执行，要么全不不执行。

2) 事务的特点

1、原子性：一组事务，要么成功；要么撤回。

2、稳定性 ：有非法数据（外键约束之类），事务撤回。

3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。

4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit 选项决定什么时候吧事务保存到日志里。

3) 事务控制语句

开启事务：BEGIN或START TRANSACTION；显式地开启一个事务；
提交事务：COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；
回滚：ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的务，并撤销正在进行的所有未提交的修改；
保存节点：SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
删除事务：RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
回滚到保存的节点：ROLLBACK TO identifier；把事务回滚到标记点；

4) mysql事务隔离级别

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

（1）Read Uncommitted（读取未提交内容）

　　在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

（2）Read Committed（读取提交内容）

　　这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

（3）Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

（4）Serializable（可串行化）

　　这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几行(Row)数据，而另一个事务却在此时插入了新的几行数据，先前的事务在接下来的查询中，就会发现有几行数据是它先前所没有的。

5) 设置mysql事务的隔离级别

打开mysql配置文件: sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf, 添加如下行。

保存配置文件，重启mysql服务。

sudo service mysql restart

django中使用事务

下面以天天生鲜项目中的订单提交为例，说明django中事务的使用：

 1 from django.db import transaction
 2 class OrderCommitView(View):
 3     '''订单创建'''
 4 
 5     @transaction.atomic  ##  使用事务
 6     def post(self,request):
 7         '''订单创建'''
 8         ##  判断用户是否登录
 9         user = request.user
10         if not user:
11             return JsonResponse({'res':0,'errmsg':'用户未登录'})
12 
13         ##  1、接收数据
14         pay_method = request.POST.get('pay_method')
15         addr_id = request.POST.get('addr_id')
16         skus_id = request.POST.get('skus_id')
17         ##  将skus_id的格式转化成列表形式
18         skus_id = skus_id.split(',')
19 
20 
21         ##  2、校验数据
22         if not all([pay_method,addr_id,skus_id]):
23             return JsonResponse({'res':1,'errmsg':'数据不完整'})
24         skus = []
25         for sku_id in skus_id:
26             ##  遍历skus_id，从数据库中查询sku
27             try:
28                 sku = GoodsSKU.objects.get(id=sku_id)
29                 skus.append(sku)
30             except GoodsSKU.DoesNoExist:
31                 return JsonResponse({'res':2,'errmsg':'商品不存在'})
32         try:
33             addr = Address.objects.get(id=addr_id)
34         except Address.DoesNoExist:
35             return JsonResponse({'res':3,'errmsg':'地址不存在'})
36         ##  校验支付方式
37         if str(pay_method) not in OrderInfo.PAY_METHODS.keys():
38             return JsonResponse({'res':4,'errmsg':'错误的支付方式'})
39         ##  设置事务的节点
40         save_id = transaction.savepoint()
41 
42         ##  3、业务处理
43         ##########  （1）先向订单数据库中添加一条数据,准备数据：order_id、user、addr、pay_method、total_count、total_price、transit_price
44         ##  计算total_count和total_price，先初始化为0，后面查询各个商品的数量和价格之后再进行累加，以及重新保存
45         try:
46             total_count = 0
47             total_price = 0
48             ##  生成order_id：格式：当前的时间+sku_id
49             order_id = datetime.now().strftime('%Y%m%d%H%M%S') + str(user.id)
50             ##  添加数据，创建新的订单数据
51             order = OrderInfo.objects.create(order_id=order_id,
52                                      user=user,
53                                      addr=addr,
54                                      pay_method=pay_method,
55                                      total_count=total_count,
56                                      total_price=total_price,
57                                      transit_price=10)
58     
59             ##########  （2）向商品订单中添加多条数据，准备数据：order、sku、count、price
60             conn = get_redis_connection('default')
61             cart_key = 'cart_%d'%user.id
62             for sku in skus:
63                 count = conn.hget(cart_key,sku.id)
64                 price = sku.price
65                 OrderGoods.objects.create(order=order,
66                                           sku=sku,
67                                           count=count,
68                                           price=price)
69                 if sku.stock < int(count):
70                     ##  业务失败时，回滚事务
71                     transaction.rollback(save_id)
72                     return JsonResponse({'res':6,'errmsg':'库存不足'})
73                 ##  MySQL数据库的库存减少，销量增加
74                 sku.stock -= int(count)
75                 sku.sales += int(count)
76                 sku.save()
77                 ##  删除Redis数据库中添加到订单中的数据
78                 conn.hdel(cart_key,sku.id)
79                 ##  累加total_count 和total_price
80                 total_count += int(count)
81                 total_price += int(count)*int(price)
82             ##  重新更新订单中的total_count和total_price
83             order.total_count = total_count
84             order.total_price = total_price
85             order.save()
86         
87         except Exception:
88             ##  业务失败时，回滚事务
89             transaction.rollback(save_id)
90             return JsonResponse({'res':7,'errmsg':'订单创建失败'})
91 
92         ##  4、返回应答
93         return JsonResponse({'res':5,'message':'创建订单成功'})

django中使用事务

参考文档：数据库事务

订单并发处理

1、订单并发问题

假设数据库中鸡腿的库存只有1，这个时候有两个用户同时提交订单，就会出现下面的情况：

　　一个用户使用一个进程，当用户1执行到2的时候，这时判断库存是1，刚好切换到进程2，所以用户2顺利下单，再切换到用户1 的时候，用户1用于之前判断库存为1，所以也能够顺利下单，这就造成了只有1个库存，却下了两个订单，显然这是不允许的，所以就必须要给进程加锁，下面是两种解决方案：悲观锁和乐观锁

2、订单并发解决

（1）悲观锁

悲观锁的原理：在查询数据库时加锁，当用户拿到锁时，其它用户不能对数据库进行操作，直到该用户对数据库操作结束时才释放锁。

加锁的语句：

select * from df_goods_info where id=sku_id for update;

django中查询数据库加锁：

sku = GoodsSKU.objects.select_for_update().get(id=sku_id)

悲观锁获取数据时对数据行了锁定，其他事务要想获取锁，必须等原事务结束。

（2）乐观锁

乐观锁原理：查询时不锁数据，提交更改时进行判断

加锁的语句：

update df_goods_sku set stock=0, sales=1 where id=sku_id and stock=1;

django更新数据库时加锁的语句：

res = GoodsSKU.objects.filter(id=sku_id, stock=orgin_stock).update(stock=new_stock, sales=new_sales)

这里注意一下：

　　乐观锁是判断更新完的数据是否与查询时的数据一样，如果一样就返回1，否则返回0。这里就存在一个问题：有两个用户同时下单，假如库存为3，当两个用户查询结束时，此时两个用户查询到的库存都是3，其中用户A拿到乐观锁，进行数据库的修改，将数据库的库存改为2，用户B拿到乐观锁后，由于此时库存与用户B之前查到的数据不一致，所以用户B下单失败。

解决方法：

　　通常我们会给用户3次机会进行数据库修改，所以可以将查询语句以及修改语句放在for循环里面。并且MySQL默认是 Repeatable Read（可重读），也就是用户A更新完的数据用户B是拿不到的，所以需要将MySQL事务隔离级别改为Read Committed（读取提交内容）

具体实例：

class OrderCommitView(View):
    '''订单创建'''
    @transaction.atomic
    def post(self, request):
        '''订单创建'''
       # 设置事务保存点
        save_id = transaction.savepoint()
        try:for sku_id in sku_ids:
                for i in range(3):
                    # 获取商品的信息
                    try:
                        sku = GoodsSKU.objects.get(id=sku_id)
                    except:
                        # 商品不存在
                        transaction.savepoint_rollback(save_id)
                        return JsonResponse({'res':4, 'errmsg':'商品不存在'})

                    # 从redis中获取用户所要购买的商品的数量
                    count = conn.hget(cart_key, sku_id)

                    # todo: 判断商品的库存
                    if int(count) > sku.stock:
                        transaction.savepoint_rollback(save_id)
                        return JsonResponse({'res':6, 'errmsg':'商品库存不足'})

                    # todo: 更新商品的库存和销量
                    orgin_stock = sku.stock
                    new_stock = orgin_stock - int(count)
                    new_sales = sku.sales + int(count)

                    # 返回受影响的行数
                    res = GoodsSKU.objects.filter(id=sku_id, stock=orgin_stock).update(stock=new_stock, sales=new_sales)
                    if res == 0:
                        if i == 2:
                            # 尝试的第3次
                            transaction.savepoint_rollback(save_id)
                            return JsonResponse({'res': 7, 'errmsg': '下单失败2'})
                        continue

                    # todo: 向df_order_goods表中添加一条记录
                    OrderGoods.objects.create(order=order,
                                              sku=sku,
                                              count=count,
                                              price=sku.price)

                    # todo: 累加计算订单商品的总数量和总价格
                    amount = sku.price*int(count)
                    total_count += int(count)
                    total_price += amount

                    # 跳出循环
                    break

            # todo: 更新订单信息表中的商品的总数量和总价格
            order.total_count = total_count
            order.total_price = total_price
            order.save()
        except Exception as e:
            transaction.savepoint_rollback(save_id)
            return JsonResponse({'res':7, 'errmsg':'下单失败'})

        # 提交事务
        transaction.savepoint_commit(save_id)
# 返回应答
        return JsonResponse({'res':5, 'message':'创建成功'})

（3）悲观锁和乐观锁的使用场景

冲突比较少的时候，使用乐观锁。
冲突比较多的时候，使用悲观锁。

MySQL事务

MySQL事务的介绍

1) 事务概念

2) 事务的特点

3) 事务控制语句

开启事务：BEGIN或START TRANSACTION；显式地开启一个事务；

提交事务：COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；

回滚：ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的务，并撤销正在进行的所有未提交的修改；

保存节点：SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；

删除事务：RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

回滚到保存的节点：ROLLBACK TO identifier；把事务回滚到标记点；

4) mysql事务隔离级别

（1）Read Uncommitted（读取未提交内容）

（2）Read Committed（读取提交内容）

（3）Repeatable Read（可重读）

（4）Serializable（可串行化）

不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几行(Row)数据，而另一个事务却在此时插入了新的几行数据，先前的事务在接下来的查询中，就会发现有几行数据是它先前所没有的。

5) 设置mysql事务的隔离级别

django中使用事务

订单并发处理

1、订单并发问题

2、订单并发解决

（1）悲观锁

（2）乐观锁

解决方法：

（3）悲观锁和乐观锁的使用场景

冲突比较少的时候，使用乐观锁。

冲突比较多的时候，使用悲观锁。