胖胖滴加菲猫

导航

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

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

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

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(锁超时时间)

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

 

posted on 2019-03-21 15:25  胖胖滴加菲猫  阅读(231)  评论(0编辑  收藏  举报