数据库三大范式以及数据库事务，数据库锁

一：数据库基本设计以及三大范式

数据库：简而言之可视为电子化的文件柜(存储电子文件的处所)，用户可以对文件中的数据进行新增、截取、更新、删除等操作。它分为关系型数据库和非关系型数据，今天我们着重来讲一下关系型数据库。

1：什么是关系型数据库？其实说白了就是把对象封装起来，然后来表述对象与对象之间的关系。

2：数据关系一般分为几种，分别是什么？数据关系有三种，分别为一对一，一对多，多对多。下面分别解释举例说明一下这三种关系

  • 一对一：比如一个人对应一张身份证，对应一个护照号，人跟身份证，人跟护照号这样就属于一对一的关系，一般两者之间的关系通过主外键或者相同主键。
  • 一对多：国家对省，省对城市，城市对区域等，这些属于一对多的关系，一般两者之间的联系是通过主外键联系的。
  • 多对多：学生对班级，老师对学生，这些是属于多对多的关系，两者之间的联系通过关系/映射/中间表。

3：上面的数据库以及之间的关系，已经解释ok，那怎么做数据设计？可以根据以下三部

  • 需求分析，调研讨论明白需要存储什么

  • 概要设计，E-R图设计，把表/属性的关系图使用powerdesigner或者codefirst画出来，解决多方面的沟通问题
  • 详细设计，把表字段对应的属性映射对应出来(一般这一步会省略掉，主要最多的是1,2步)

4：数据库设计一般会遵循三大范式，分别是什么？下面解析说明。

  A：每一列保持原子性，不可分割；

学员ID 学员名字 家庭信息
1 可可 3口人，河南
2 馨馨 4口人，上海

 如上图的家庭信息这一栏，我们如果这样写，那就是违背了原子性，因为家庭信息是可以分割的，可以分拆为家庭人员数，户籍所在地，可以拆分成如下：

学员ID 学员名字 家庭人员  信息
1 可可 3口人  河南
2 馨馨 4口人

 上海

B：每一列都得跟主键相关，一张表只应该描述一个对象；这个需要注意两点，一是：主键 ，二是：信息跟主键相关

ID 员工名字 公司名字 公司地址
1 可可 缘爱 上海
2 馨馨 可馨

 深圳

比如上面的员工表，里面增加了员工公司的一些信息，这样就违背了第二大范式，通过修改如下下面即可：

 员工表：

ID 员工名字 公司Id
1 可可 1
2 馨馨 2

公司表：

ID 公司名字 公司地址
1 缘爱 上海
2 可馨

 深圳

   C：每一列都得跟主键直接相关，而不是间接相关，如下面的公司名称就属于违背了第三大范式，但是有时候为了方便查询，第三大范式经常会被违背的，这个还要根据具体的业务逻辑去选择的。

ID 员工名字  公司Id 公司名字
1 可可  1 缘爱
2 馨馨  2 可馨

以上三大范式只是建议，其实都有可能被违背，话说一将不行，累死三军，所以正确的设计，会减少很多工作量。

二：数据库事务

1：什么是事务？事务是多条sql作为一个整体提交给数据库系统，要么全部执行完成，要么全部取消。是一个不可分割的逻辑单元。其实我们经常见到的单条update或者insert就是一个简单的事务。

2：为什么要用事务？事务保证一起成功或者一起失败，比如sql语句，一个下订单成功；另一个减掉库存；如果不使用事务，有可能刚刚执行下单成功，但是在执行减掉库存的时候错误，则会造成订单和库存不能保证统一的。

3：事务的基本语法

 1 ******************************事务sql***********************************
 2 
 3 ---开启事务
 4  begin tran
 5  --错误扑捉机制，看好啦，这里也有的。并且可以嵌套。
 6  begin try  
 7     --语句正确
 8     insert into [Company] (Name,CreateTime,CreatorId) values ('字节','2019-03-20',1)
 9     --CreatorId为int类型，出错
10     insert into [Company] (Name,CreateTime,CreatorId) values ('新启','2019-03-20','Test')
11     --语句正确
12     insert into [Company] (Name,CreateTime,CreatorId) values ('舞动','2019-03-20',2)
13  end try
14  begin catch
15     select Error_number() as ErrorNumber,  --错误代码
16            Error_severity() as ErrorSeverity,  --错误严重级别，级别小于10 try catch 捕获不到
17            Error_state() as ErrorState ,  --错误状态码
18            Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
19            Error_line() as ErrorLine,  --发生错误的行号
20            Error_message() as ErrorMessage  --错误的具体信息
21     if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
22        rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
23  end catch
24  if(@@trancount>0)
25  commit tran  --如果成功Company表中，将会有3条数据。
26  
27  --表本身为空表，ID ,Numb为int 类型，其它为nvarchar类型
28  select * from [Company]
29 
30 
31 
32 ******************************事务含save tran***********************************
33 
34 ---开启事务
35  begin tran
36  --错误扑捉机制，看好啦，这里也有的。并且可以嵌套。
37  begin try  
38     --语句正确
39     insert into [Company] (Name,CreateTime,CreatorId) values ('字节','2019-03-20',3)
40     --加入保存点，如果下面的出错了，上面的还能保存
41     save tran SavePoint
42     --CreatorId为int类型，出错
43     insert into [Company] (Name,CreateTime,CreatorId) values ('新启','2019-03-20','Test')
44     --语句正确
45     insert into [Company] (Name,CreateTime,CreatorId) values ('舞动','2019-03-20',4)
46  end try
47  begin catch
48     select Error_number() as ErrorNumber,  --错误代码
49            Error_severity() as ErrorSeverity,  --错误严重级别，级别小于10 try catch 捕获不到
50            Error_state() as ErrorState ,  --错误状态码
51            Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
52            Error_line() as ErrorLine,  --发生错误的行号
53            Error_message() as ErrorMessage  --错误的具体信息
54     if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
55        rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
56  end catch
57  if(@@trancount>0)
58  rollback tran SavePoint  --如果成功Company表中，将会有3条数据。
59  
60  --表本身为空表，ID ,Numb为int 类型，其它为nvarchar类型
61  select * from [Company]
View Code
--开始事务
begintransaction tran_bank;
declare @tran_error int;
set @tran_error = 0;
begin try
update bank set totalMoney = totalMoney - 10000 where userName = 'jack';        
set @tran_error = @tran_error + @@error;
update bank set totalMoney = totalMoney + 10000 where userName = 'jason';
set @tran_error = @tran_error + @@error;
end try
begin catch        
print'出现异常，错误编号：' + convert(varchar, error_number()) + '， 错误消息：' + error_message(); 
set @tran_error = @tran_error + 1;
end catch
if (@tran_error > 0)
begin
        --执行出错，回滚事务
rollbacktran;
print'转账失败，取消交易';
end
else
begin
        --没有异常，提交事务
committran;
print'转账成功';
end
go
View Code

4：事务的特点为ACID

原子性：要么都成功，要么都失败，一个事务中不可能停滞在中间某个环节。事务发送问题就会被回滚到事务执行之前的状态，就像没有执行过一样。

一致性：如果事务是并发，系统也会把事务串起来，一个一个的按序执行。

隔离线：AB两个事务同时操作一张表，B事务要么是在A事务前完成，要么是在A事务完成后执行，事务操作表的时候会锁表

持久性：事务一旦提交，则数据就会被固化下来，持久存在，直到下次再被修改。

三：数据锁

1：为什么要使用锁？

多数据同时访问一个数据资源时，保证操作有个先后顺序管理，处理并发问题，防止数据

A：修改丢失 例：多并发操作一张表，一个读，一个删除或者修改

B：不可重复读 例：多并发操作一张表，一个读，一个改或者删除

C：脏读/幻读 例：多并发操作一张表，一个修改一半，一个读取

2：数据锁一般分为几种？

A：乐观锁：认为没有并发，读取数据--更新数据---保存，比如更新数据时做一个判断，可以使用时间戳/Version/检查更新字段/检查全部字段作为where条件，其实这个是按照业务逻辑来定的，不过要保证所有的操作都按照这个规范来操作，不然也会有漏洞，比如通过其它渠道更新，乐观锁的好处是性能高。

B：悲观锁：认为任何时候都有可能有多线程并发，比如读数据时别人恰好在修改。悲观锁是基于数据锁的机制来完成，它分为：

  • 共享锁 S锁 读锁，允许别的事务来读，但是不允许修改；读完就释放，锁定数据页；(除非holdlock就一直锁定)
  • 排他锁 X锁 写锁，准备写数据，不允许读也不允许写；
  • 更新锁 U锁   ，先查询再更新；

其实按照其它的方式可以分为行锁（where id=3），表锁(where 1=1)

3：怎么避免死锁？

  • 不用锁就不会死锁，乐观锁
  • 统一操作顺序--先A后B再C
  • 最小单元锁，锁里面操作尽量减少
  • 避免事务中等待用户输入
  • 减少数据库并发
  • 分库分表表分区
  • 降低事务级别
  • 设置死锁时间  set lock_timeout（锁超时时间）

备注：其实高并发下死锁是不可能避免的，只能减少。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/loverwangshan/p/10570446.html