开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> 数据库优化-事务篇 -> 正文阅读

[大数据]数据库优化-事务篇

数据库优化-事务篇

说明

背景: 日常开发中, 性能的瓶颈往往在于 IO, 尤其是数据库. 了解如何优化数据库, 提高数据库的性能和请求响应速度是有必要的.因此, 我打算梳理和总结数据库优化的相关内容.

本篇为事务篇, 讲解数据库事务相关的优化. 梳理事务相关的知识点, 并结合实际场景进行讲解.
文章中的实例都是以 PG 数据库为例的, 其它数据库语法可能稍有不同.

准备数据:

create table public.test (
 id serial,
 val varchar(20) null,
    num int,
    version int default 0 not null
);

insert
 into
 test (val, num)
values('iPhone13', 200);

insert
 into
 test (val, num)
values('MacBook', 300);

insert
 into
 test (val, num)
values('华为P20', 100);

为什么要有数据库事务

数据库事务是数据库中极为重要的一部分.

一个经典的例子就是银行转账. 由于关于事务的讲解在网上已有很多较为优秀的文章了, 本文就不再赘述.

可以参考:

深入理解数据库事务

注意: 无论那种事务的隔离级别, 更新的时候都会取最新的已提交的数据. 即为当前读

数据竞争

在多个数据库 session 并发执行 sql 时, 会遇到数据竞争 data race.

数据竞争: 多个数据库事务尝试在同一时间更新同一行的数据. 为了保持数据的可靠性, 在更新同一条数据时, 需要获取写锁. 而写锁会阻止其它 session 对数据的修改. 想修改这条数据的其它 session 必须等待.

数据竞争是无法避免的, 只能尽可能地降低其对性能的影响.

下图是一个非常简单的例子.

请添加图片描述

如何降低数据竞争对性能的影响

常见的数据库(例如: Oracle,MySQL,Postgresql) 采用 锁 + MVCC的方案实现尽可能地降低数据竞争.
从而提高数据库并发访问, 维护数据一致性(data consistency)和实现不同的事务的隔离级别.

锁机制

数据库的锁机制, 比较复杂, 属于数据库内部的实现, 我们业务开发人员一般不会去主动申请锁的.
拥有这么多种类的锁, 主要目的是: 提高并发, 减少数据竞争.

以 PG 数据库为例, 锁大致的分类如下:

不同颗粒度的锁

表锁
行锁
页锁 (完全由数据库管理)

不同种形式的锁

共享锁 share 有时也被称为读锁
排它锁 exclusive 有时也被称为写锁

具体的锁, 见 PG 文档: explicit-locking

一条 sql 语句, 可以获取多个锁, 例如

begin;
-- 行的排它锁, 避免其它事务修改同一行数据
-- 表的共享锁, 避免有alert等操作修改表结构
update
 test
set
 num = num - 200
where
 val = 'iPhone13';

commit;

-- pg 查看当前数据库的锁
select * from pg_catalog.pg_locks

多版本并发控制 (Multiversion concurrency control 简称 MVCC)

使用 MVVC 最大的优势是: 读从不阻止写, 写从不阻止读.

The main advantage of using the MVCC model of concurrency control rather than locking is that in MVCC locks acquired for querying (reading) data do not conflict with locks acquired for writing data, and so reading never blocks writing and writing never blocks reading.

详情可以见:

注释: MySQL Oracle 是依靠 undo log 实现 MVCC 的, 而 PG 是直接插入数据到数据库中, 然后根据 tuple 中的 xmax,xmin 来实现的. 不同数据库实现 MVCC 的方式不太相同.

事务相关性能优化

MVCC 和锁机制属于数据库本身的优化, 那么在我们的应用代码, 可以使用什么方式进行性能优化呢?

减少长事务

减少长事务, 避免事务持续太长时间(超过 8s 就可以认为是很长的事务了).

长事务带来的不利影响:

锁住太多的资源，造成大量阻塞，高并发情况下容易造成数据库连接池撑爆
Mysql, Oracle 的回滚日志 undo log 暴增
PG 可能会造成表膨胀

可以通过下面的方式, 尽量避免:

将一些非核心的 sql, 移除到事务之外; 例如实时性要求不高的查询语句等
非数据库的操作放到事务之外, 例如长时间的计算或者加密算法, rpc, http 调用等
如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

例如:

ActiveRecord::Base.transaction do
  exist = Account.where(username: 'steven').exists?
  return if exist
  account = Account.create(username: 'steven')
  AccountHistory.create(account: account)
  Rpc.call('account')
  Rails.logger.info('xxxx')
end

可以优化为:

exist = Account.where(username: 'steven').exists?
return if exist
ActiveRecord::Base.transaction do
  account = Account.create(username: 'steven')
  AccountHistory.create(account: account)
end
Rpc.call('account')
Rails.logger.info('xxxx')

如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

假设要实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，这个业务需要涉及到以下操作：

从顾客 A 账户余额中扣除电影票价；
给影院 B 的账户余额增加这张电影票价；
新增一条交易日志。

那么, 最为合理的顺序是 3, 1, 2

原因是: 新增记录是不会锁住任何行的, 因此 3 放到最开始是合适的. 顾客 A 的余额大概率在同一时间不会和其它事务冲突.
而, 影院 B 的账户余额大概率会被多个事务所修改. 因此将 2 放到最后是最合适的.

PG 减少使用子事务

具体原因: PostgreSQL Subtransactions Considered Harmful

悲观锁与乐观锁

本节部分内容, 摘抄自文章: 悲观锁与乐观锁的实现(详情图解)
也推荐大家去看看

乐观锁和悲观锁是两种思想，用于解决并发场景下的数据竞争问题。
这两种方案没有绝对的优劣, 分别适用于不同场景.

悲观锁（Pessimistic Lock）：就是很悲观，每次去拿数据的时候都认为别人会修改。所以每次在拿数据的时候都会上锁。这样别人想拿数据就被挡住，直到悲观锁被释放，悲观锁中的共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程

但是在效率方面，处理加锁的机制会产生额外的开销，还有增加产生死锁的机会。另外还会降低并行性，如果已经锁定了一个线程 A，其他线程就必须等待该线程 A 处理完才可以处理

数据库中的行锁，表锁，读锁（共享锁），写锁（排他锁），以及 Java 中的 synchronized 锁均为悲观锁

乐观锁（Optimistic Lock）：就是很乐观，每次去拿数据的时候都认为别人不会修改。所以不会上锁，但是如果想要更新数据，则会在更新前检查在读取至更新这段时间别人有没有修改过这个数据。

如果修改过，则重新读取，再次尝试更新，循环上述步骤直到更新成功（当然也允许更新失败的线程放弃操作）,乐观锁适用于多读的应用类型，这样可以提高吞吐量
相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。

一般的实现乐观锁的方式就是记录数据版本（version）或者是时间戳来实现，不过使用版本记录是最常用的。

我们看看实际的例子

现在有多个数据库 session, 想修改 iPhone13 的库存 num, 而库存 num 是不能小于 0 的.

场景:

Session A：查询到 num=200,做了库存减量成了 0
Session B：事务启动后，查询到也是 200，等 A 释放了行锁，B 进行 update，直接变成 -200
但是 Session B 查询时，时有库存的，因此才减库存，结果变成负的。

对于这种场景，怎么避免减成负值?

首先最为简单有效的是使用悲观锁:

begin;
-- for update 会获取 排它锁, 其它事务则无法读取或修改, 可以防止其它事务的干扰
select * from test where val = 'iPhone13' for update;

-- 可以通过pg_locks 看到当前事务获取了 ExclusiveLock 排他锁
select
 transactionid ,
 locktype ,
 "mode"
from
 pg_catalog.pg_locks
where
 transactionid = txid_current()::int;

update
 test
set
 num = num - 200
where
 val = 'iPhone13';

commit;

请添加图片描述

悲观锁固然能够解决并发带来的数据竞争, 但是会较为严重地影响并发量.

根据实际情况, 用户大部分情况会看库存, 而不会真的去买, 从而修改库存, 我们使用乐观锁会更合适一些.
不过, 有的时候可能反过来, 大部分情况会修改数据, 而很少看数据, 则使用悲观锁合适一些.

begin;
-- 先查出 iPhone13 的版本号, 为 0
select * from test where val = 'iPhone13';

-- 减少库存 200, 并将版本号 + 1  如果此时有其它事务修改了数据, 则version也会被+1, 此时version = 1
-- 则, 下面的更新语句不会影响任何数据
update
 test
set
 num = num - 200,
 version = version + 1
where
 val = 'iPhone13'
 and version = 0;

commit;

如果更新的行数为0, 则表示没有更新成果, 应用段代码可以重试.

库存的场景, 可以再精简一些, 只要满足有足够的商品给客户即可, 对版本的要求就可以放低.

update
 test
set
 num = num - 200
where
 val = 'iPhone13'
  and num >= 200;

update 语句的 affected_rows,如果等于 1 那就是符合预期；如果等于 0，那表示库存不够减了，业务要处理一下去，比如提示“库存不足”

死锁和死锁检测

本节大量摘抄自: MySQL 实战 45 讲 07 | 行锁功过：怎么减少行锁对性能的影响？

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

一旦形成死锁, 会造成事务堆积, 数据库很容易就崩了. 为了消除死锁的影响, 数据库一般会提供死锁探测, 如果发现有死锁, 则会回滚, 使得事务可以正常完成.

-- 在 psql 中, 可以看到死锁的超时时间, PG默认是 1s
show deadlock_timeout;

举例:

-- Session 1
begin;
-- T1 更新iPhone13的库存, 数据行被锁
update test set num = num - 20 where val = 'iPhone13';
-- T3 想更新MacBook的数据,但是在T2时,已被锁
update test set num = num - 20 where val = 'MacBook';

-- Session 2
begin;
-- T2 更新MacBook的库存
update test set num = num - 20 where val = 'MacBook';
-- T4 想更新iPhone13的数据, 但是在T1已被Session 1锁住
-- 此时, PG会死锁检测使得 Session 2 rollback
update test set num = num - 20 where val = 'iPhone13';

Session 2 被检测出死锁的报错

SQL 错误 [40P01]: 错误: 检测到死锁
Detail: 进程 48092 等待在事务 747 上的 ShareLock; 由进程 52488 阻塞.
进程 52488 等待在事务 748 上的 ShareLock; 由进程 48092 阻塞.
Hint: 详细信息请查看服务器日志.
Where: 当更新关系"test"的元组(0, 6)时

虽然死锁可以被数据库检测到, 但是我们还是要尽量避免死锁的.
其中可以通过调整sql的执行顺序. 例如: 如果Session 2 执行sql的顺序与Session 1一致. 则不会发生死锁.

The best defense against deadlocks is generally to avoid them by being certain that all applications using a database acquire locks on multiple objects in a consistent order.

注意: 死锁的检测是比较消耗资源的

场景: iPhone13秒杀活动开始了, 在短暂时间内, 有大量请求执行下面的sql

update test set num = num - 1 where val = 'iPhone13';

此时, 会发现数据库撑不住了, 但是查看并发的请求量每秒可能还不到100, CPU却快到100%了.
出现了, 热点数据更新问题.

原因是:

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作, 而整体的死锁检测是O(n^2)级别的。
假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100,000 这个量级的。
虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。

因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

死锁检测是需要保持的, 但是如何减少死锁检测对数据库的影响呢?

主要方向: 控制访问相同资源的并发事务量。

具体的方法有:

拆行，一行拆多行

你可以考虑通过将一行改成逻辑上的多行来减少锁冲突。还是以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。

-- 可以尝试将库存放入多行. 比如: iPhone13有200台, 可以放入10行, 每一行有20台. 需要减少库存时,则随便选中一条大于0的记录. 
-- 以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。
update test set num = num - 1 where val = 'iPhone13-1';
update test set num = num - 1 where val = 'iPhone13-2';
-- ... 多行iPhone13的库存数据