正文

MySQL 事物和数据库锁

IP云
IP云V管理员/2 阅读
09/19
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

1.数据库事物

1. 什么是事务 
事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。 
事务的结束有两种，当事务中的所以步骤全部成功执行时，事务提交。如果其中一个步骤失败，将发生回滚操作，撤消撤消之前到事务开始时的所以操作。

总结:事物就是一组操作,要么全部完成,要么全部失败!

2.事物特性 ACID

事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性。

1 、原子性  (事物内的操作,要么全部成功,要么全部失败)
事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

2 、一致性 (事物之前之后,前后数据的一致性)
事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态如果数据库系统

运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是
不一致的状态。

3 、隔离性(多个事物时,相互不能干扰) 
一个事务的执行不能被其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

   4 、持续性(一旦事物提交(commit)之后,是不可回滚的) 
也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

3.例子:  现实中的转账操作:

  1. 开启事物  start transaction
  2. 提交事物   commit
  3. 回滚事物   rollback

1.创建表

create table account(
id int(50) not null auto_increment primary key,
a_name VARCHAR(50) not null,
money DOUBLE(5,2) not NULL
);

2.插入数据

insert into account (a_name,money) values('鲁班',159.11),('后羿',333);

3.执行转账

start transaction; -- 开启事物
-- 执行sql语句操作
update account set money = money - 500 where id ='1';
update account set money = money+500 where id = '2';commit; -- 手动提交事物rollback; -- 回滚事物 -- 查看结果
select * from account;

2.数据库锁

1. 锁的基本概念

当并发事务同时访问一个资源时，有可能导致数据不一致，因此需要一种机制来将数据访问顺序化，以保证数据库数据的一致性。

2. 锁的基本类型

数据库上的操作可以归纳为两种：读和写。

多个事务同时读取一个对象的时候，是不会有冲突的。同时读和写，或者同时写才会产生冲突。因此为了提高数据库的并发性能，通常会定义两种锁：共享锁和排它锁。

2.1 共享锁（Shared Lock，也叫S锁）

共享锁(S)表示对数据进行读操作。因此多个事务可以同时为一个对象加共享锁。（如果试衣间的门还没被锁上，顾客都能够同时进去参观）

2.2 排他锁(Exclusive Lock，也叫X锁)

    排他锁也叫写锁。

排他锁表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。(某个顾客把试衣间从里面反锁了，其他顾客想要使用这个试衣间，就只有等待锁从里面给打开了)

3.开发中常见的两种锁:

3.1悲观锁(Pessimistic Lock), 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block(阻塞)直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制.

注：要使用悲观锁，我们必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。关闭自动提交命令为：set autocommit=0;

设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：

//0.开始事务
start transaction;//1.查询出数据信息
select * from ren where p_id='p001' for update;//2.修改当前人员信息
update ren set p_name='张1丰' where p_id='p001';//3. 提交事务
commit;

在另外的查询页面执行:

select * from ren where p_id='p001' for update;

会发现当前查询会进入到等待状态,不会显示出数据,当上面的sql执行完毕提交事物后,当前sql才会显示结果.

上面的第一步我们执行了一次查询操作：select * from ren where p_id='p001'  for update;

与普通查询不一样的是，我们使用了select…for update的方式，这样就通过数据库实现了悲观锁。此时在ren表中，id为p001的 那条数据就被我们锁定了，其它的事务必须等本注：需要注意的是，在事务中，只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE(共享锁) 同一笔数据时会等待其它事务结束后才执行，一般SELECT ... 则不受此影响。拿上面的实例来说，当我执行select * from ren where p_id='p001' for update;后。我在另外的事务中如果再次执行select * from ren where p_id='p001' for update;则第二个事务会一直等待第一个事务的提交，此时第二个查询处于阻塞的状态，但是如果我是在第二个事务中执行select * from ren where p_id='p001';则能正常查询出数据，不会受第一个事务的影响。

补充：MySQL select…for update的Row Lock与Table Lock

上面我们提到，使用select…for update会把数据给锁住，不过我们需要注意一些锁的级别，MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。

注意: 悲观锁的确保了数据的安全性，在数据被操作的时候锁定数据不被访问，但是这样会带来很大的性能问题。因此悲观锁在实际开发中使用是相对比较少的。

3.2 乐观锁(Optimistic Lock), 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

使用乐观锁的两种方式:

1.使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现 方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录 的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数 据。

2.乐观锁定的第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳 （datatime）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

代码实例:

update account set name='ygz' , version = version+1 where id = '1' and version = 1;

两种锁各有优缺点:不可认为一种好于另一种，像乐观锁适用于写入比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry(重试)，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适.

4.数据类型介绍

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
       bit[(M)]
           二进制位（101001），m表示二进制位的长度（1-64），默认m＝1
 
       tinyint[(m)] [unsigned] [zerofill]
 
           小整数，数据类型用于保存一些范围的整数数值范围：
           有符号：
               -128 ～ 127.
           无符号：
           ～ 255
 
       int[(m)][unsigned][zerofill]
 
           整数，数据类型用于保存一些范围的整数数值范围：
               有符号：
                   -2147483648 ～ 2147483647
               无符号：
            ～ 4294967295
 
           特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002
 
       bigint[(m)][unsigned][zerofill]
           大整数，数据类型用于保存一些范围的整数数值范围：
               有符号：
                   -9223372036854775808 ～ 9223372036854775807
               无符号：
～  18446744073709551615
 
       decimal[(m[,d])] [unsigned] [zerofill]
           准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
 
           特别的：对于精确数值计算时需要用此类型
                  decaimal能够存储精确值的原因在于其内部按照字符串存储。
 
       FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
           单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
               无符号：
                   -3.402823466E+38 to -1.175494351E-38,
                   1.175494351E-38 to 3.402823466E+38
               有符号：
                   1.175494351E-38 to 3.402823466E+38
 
           **** 数值越大，越不准确 ****
 
       DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
           双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
 
               无符号：
                   -1.7976931348623157E+308 to -2.2250738585072014E-308
                   2.2250738585072014E-308 to 1.7976931348623157E+308
               有符号：
                   2.2250738585072014E-308 to 1.7976931348623157E+308
           **** 数值越大，越不准确 ****
 
 
       char (m)
           char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
           PS: 即使数据小于m长度，也会占用m长度
       varchar(m)
           varchar数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。
 
           注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡
 
       text
           text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。
 
       mediumtext
           A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
 
       longtext
           A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.
 
 
       enum
           枚举类型，
           An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
           示例：
               CREATE TABLE shirts (
                   name VARCHAR(40),
                   size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
               );
               INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
 
       set
           集合类型
           A SET column can have a maximum of 64 distinct members.
           示例：
               CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
               INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
 
       DATE
           YYYY-MM-DD（1000-01-01/9999-12-31）
 
       TIME
           HH:MM:SS（'-838:59:59'/'838:59:59'）
 
       YEAR
           YYYY（1901/2155）
 
       DATETIME
 
           YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）
 
       TIMESTAMP
 
           YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）