澳门十大赌场最新排名 > 澳门十大赌场 > 创建_Log表及触发器,InnoDB锁冲突案例演示

原标题:创建_Log表及触发器,InnoDB锁冲突案例演示

浏览次数:200 时间:2019-10-04

1 视图介绍

         视图是从三个依然几个基本表(或视图)导出的表。它与基本表差别,是一个虚表。数据库中只存放视图的概念,而不设有视图对应的数据,这几个多少依旧贮存在本来的基本表中。所以如若基本表中的数据产生变化,从视图中询问的数目也就跟着转移了。从这些意义上讲,视图仿佛三个窗口,透过它能够看来数据库中和谐感兴趣的多少及其浮动。

1.1 视图的概述

      视图是从二个或然多个表导出的,它的作为与表极其相像,但视图是一个设想表,在视图中能够使用SELECT语句询问数据,以及利用insert、update和delete语句修改记录,对于视图的操作最终转化为对骨干数据表的操作。视图不只可以够一本万利操作,而且可以保持数据库系统的安全性。

澳门十大赌场最新排名, 视图一经定义便蕴藏在数据库中,与其相呼应的数据并不曾像表数据那样在数据库中在积攒一份,通过视图见到的数量只是存放在基本表中的数据。能够对其进展增删该查,通过视图对数码修改,基本表数据也对应变化,反之亦然。

 

实例效果:

1.2 使用视图的指标与收益

1.聚集特定数据:使顾客只好看看和操作与他们关于的数据,升高了数码的安全性。
2.简化数据操作:使顾客不用写复杂的查询语句就可对数据开展操作。
3.定制客商数量:使不一样档期的顺序的客户能以分化的不二法门来看差别的数目。
4.联合分离数量:视图能够从品位和垂直方向上划分数据,但原数据库的布局保持不改变。

Preface

落到实处表数据的增修删时,记录日志。

2 创制视图

语法:

 [ with check option ] --强制所有通过是同修改的数据,都要满足select语句中指定的条件
select查询语句
as
[ with encryption ] --用于加密视图的定义,用户只能查看不能修改。
[ (列名表) ]
create view 视图

先创设三个学生表

use marvel_db;
--创建一个学生表
create table stuTable(
    id int identity(1,1)primary key,--id 主键,自增
    name varchar(20),
    gender char(2),
    age int,
)
--往表中插入数据
insert into stuTable (name,gender,age)
values
    ('刘邦','男',23),
    ('项羽','男',22),
    ('韩信','男',21); 
insert into stuTable(name,gender,age) values('萧何','男',24)  

开创视图

--创建视图
if (exists (select * from sys.objects where name = 'stu_view'))
    drop view stu_view
go
--stu_view()不实用参数,默认为基础表中的列名称
--注意 create view 必须是批处理里面的语句
create view stu_view 
as 
select name,age from stuTable where age>20;
go
--执行视图
select * from stu_view;  

询问结果:

澳门十大赌场最新排名 1

 

1.“复制”现有表,

3 修改视图

 

go
alter view stu_view 
as
select * from stuTable where age>22;
go
select * from stu_view  

突显结果:

澳门十大赌场最新排名 2

    As we know,InnoDB is index organized table.InnoDB engine supports row-level lock base on indexes,if there're no indexes on a certain table the record locks will upgrade to "table-level"(not really table lock,just locks all the records in the table) locks.Furthe more,in RR transaction isolation mode,It's more complicated.'cause there're gap locks(together with record locks,we call them next key locks) to prevent phantom read between multiple tansactions.Let's do some test watch the locking conflicts.

   成立相应的_Log表;

4 删除视图

 

go
--语法
drop view view_name1,view_name2,......,view_nameN;
--该语句可以同时删除多个视图,只要在删除各视图名称之间用逗号分隔即可。

举个例子说:删除视图 stu_view

--语法
drop view stu_view;
--该语句可以同时删除多个视图,只要在删除各视图名称之间用逗号分隔即可。  

5 通过视图管理表中的数据

(1).通过视图向基本表中插入数据

注意:

1.可经过视图向基表中插入数据,但插入的多寡实际上寄存在基表中,实际不是寄放在在视图中。

2.一旦视图援引了几个表,使用insert语句插入的列必需属于同二个表。

3.若成立视图时定义了“with check option”选项,则应用视图向基表中插入数据时,必需确定保证插入后的数码满意定义视图的范围标准。

--(1).通过视图向基本表中插入数据
go
create view stu_insert_view(编号,姓名,性别,年龄)
as
select id,name,gender,age from stuTable;
go
select * from stuTable;
---插入一条数据
insert into stu_insert_view values('孙权','男',34);
----查看插入记录之后表中的内容。
select * from stuTable;  

来得结果:

澳门十大赌场最新排名 3

(2).通过视图修改基本表的数量

--查看修改之前的数据
select * from stuTable;  

展示结果:

澳门十大赌场最新排名 4

--修改数据
update stu_insert_view set 年龄=30 where 姓名='刘邦';
--查看修改后的数据
select * from stuTable;  

结果呈现:

澳门十大赌场最新排名 5

(3).通过视图删除基本表的多寡

注意:

1.要删减的数目必需含有在视图的结果聚焦。

2.万一视图引用了多个表时,不可能用delete命令删除数据。

语法

--语法
delete stu_insert_view where condition;  

剔除从前:

澳门十大赌场最新排名 6

删除:

--例子
delete stu_insert_view where 姓名 ='刘邦';
select * from stu_insert_view;
select * from stuTable;  

显示结果:

澳门十大赌场最新排名 7

 

 (注意点:

6总结

1.用到景况:

1.时常应用的查询,或较复杂的一齐查询相应创设视图,那是会优化性能的
2.涉嫌到权力管理方面,比方某表中的部分字段含有机密音信,不应有让低权限的客商采访到的图景,那时候给那一个客户提供三个符合他们权限的视图,供他们观看本人的数目就行了。
2.视图与表的分别:

1.视图是一度编写翻译好的SQL语句,是基于SQL语句的结果集的可视化表,而表不是;
2.视图(除过索引视图)未有实际的情理记录,而基本表有;
3.意味内容,视图是窗口;
4.表占物理空间,而视图不占物理空间,视图只是逻辑概念的存在;
5.视图是查看数据表的一种方法,能够查询数据表中有些字段构成的数码,只是局部SQL语句的集结。从安全角度说,视图可避防备客商接触数据表,进而不知表结构;
6.表属于全局方式的表,是实表;视图数据局地格局的表,是虚表;
7.视图的树立和删除只影响视图本人,不影响对应的基本表。

 

转载自:

Procedure

通过select union all 的方式,避免了IDENTITY 的“复制”,
即如果原表有 PK 如 ID Identity,_Log表 仅“复制”ID int,“不复制” Identity属性,
以便 Insert Update Delete时,可以Insert到Log表。)

 

2.对现成表,创设Insert,Update,Delete的触发器,

Crete a test table as below.

  并将相应数额 记录到对应的_Log表

 1 zlm@192.168.56.100:3306 [zlm]>create table t1(
 2     -> c1 int unsigned not null default '0',
 3     -> c2 int unsigned not null default '0',
 4     -> c3 int unsigned not null default '0',
 5     -> c4 int unsigned not null default '0',
 6     -> primary key(c1),
 7     -> key(c2)
 8     -> ) engine=innodb;
 9 Query OK, 0 rows affected (0.02 sec)
10 
11 zlm@192.168.56.100:3306 [zlm]>insert into t1(c1,c2,c3,c4) values(0,0,0,0),(1,1,1,0),(3,3,3,0),(4,2,2,0),(6,2,5,0),(8,6,6,0),(10,4,4,0);
12 Query OK, 7 rows affected (0.01 sec)
13 Records: 7  Duplicates: 0  Warnings: 0
14 
15 zlm@192.168.56.100:3306 [zlm]>select * from t1;
16  ---- ---- ---- ---- 
17 | c1 | c2 | c3 | c4 |
18  ---- ---- ---- ---- 
19 |  0 |  0 |  0 |  0 |
20 |  1 |  1 |  1 |  0 |
21 |  3 |  3 |  3 |  0 |
22 |  4 |  2 |  2 |  0 |
23 |  6 |  2 |  5 |  0 |
24 |  8 |  6 |  6 |  0 |
25 | 10 |  4 |  4 |  0 |
26  ---- ---- ---- ---- 
27 7 rows in set (0.01 sec)
28 
29 zlm@192.168.56.100:3306 [(none)]>select @@transaction_isolation;
30  ------------------------- 
31 | @@transaction_isolation |
32  ------------------------- 
33 | REPEATABLE-READ         | //Make surej in RR transaction isolation level.
34  ------------------------- 
35 1 row in set (0.00 sec)
36 
37 zlm@192.168.56.100:3306 [(none)]>show variables like 'innodb_status_output_locks';
38  ---------------------------- ------- 
39 | Variable_name              | Value |
40  ---------------------------- ------- 
41 | innodb_status_output_locks | ON    |
42  ---------------------------- ------- 
43 1 row in set (0.00 sec)

 

 

对应代码如下:

**Test 1. session1 executes "select ...  for update" and session2 executes "select ... lock in share mode".(conflict)**

BEGIN TRAN   
BEGIN TRY  


--定义TAB_CURSOR
DECLARE TAB_CURSOR CURSOR read_only
FOR
   SELECT name FROM SysObjects Where XType='U' 
  -- AND name = N'T01ConstItem' 
  and [name] <> N'dtproperties'
   ORDER BY Name;

--打开
OPEN TAB_CURSOR

DECLARE @P_TabName NVARCHAR(200);
DECLARE @P_TabName_Log NVARCHAR(200);
DECLARE @P_Create_Log_Tab NVARCHAR(4000);
DECLARE @P_Create_Trig_I NVARCHAR(4000);
DECLARE @P_Create_Trig_U NVARCHAR(4000);
DECLARE @P_Create_Trig_D NVARCHAR(4000);

FETCH NEXT FROM TAB_CURSOR 
           INTO @P_TabName
--循环
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
    BEGIN   
    SET @P_TabName_Log = CONCAT(@P_TabName,N'_Log');

    SET @P_Create_Log_Tab = N' SELECT * ';
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,N''I '' AS Action');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,Getdate() AS ActionDate ');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' INTO ');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,@P_TabName_Log );
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' FROM  ' );
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,@P_TabName);
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' UNION ALL ');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' SELECT TOP (1) * ');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,N''I '' AS Action');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,Getdate() AS ActionDate ');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' FROM  ' );
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,@P_TabName);
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab, N' WHERE 1=0 ; ');

    EXEC( @P_Create_Log_Tab);

    --SET @P_Create_Log_Tab = CONCAT(N' SET IDENTITY_INSERT ',@P_TabName_Log ,' ON '); 
    --EXEC( @P_Create_Log_Tab);


    SET @P_Create_Trig_I = N' create trigger ';
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I,N' trig_',@P_TabName,N'_I ');
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I,N' on ',@P_TabName,N' after INSERT as ');
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I, N' begin ');    
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I, N'insert into ',@P_TabName_Log );
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I,N' select * , N''I'',Getdate() from Inserted ; ' );
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I, N'end ');

    --select @P_Create_Trig_I;

    EXEC( @P_Create_Trig_I);

    SET @P_Create_Trig_U = N' create trigger ';
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' trig_',@P_TabName,N'_U ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' on ',@P_TabName,N' after UPDATE as ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N' begin ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'insert into ',@P_TabName_Log );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' select * , N''UD'',Getdate() from Deleted ; ' );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'insert into ',@P_TabName_Log );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' select * , N''UI'',Getdate() from Inserted ; ' );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'end ');
    EXEC( @P_Create_Trig_U);

    SET @P_Create_Trig_U = N' create trigger ';
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' trig_',@P_TabName,N'_D ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' on ',@P_TabName,N' after DELETE as ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N' begin ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'insert into ',@P_TabName_Log );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' select * , N''D'',Getdate() from Deleted ; ' );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'end ');
    EXEC( @P_Create_Trig_U);

    END
   FETCH NEXT FROM TAB_CURSOR INTO @P_TabName
END

--关闭
CLOSE TAB_CURSOR
--释放
DEALLOCATE TAB_CURSOR

COMMIT TRAN;  

END TRY  
BEGIN CATCH  
    SELECT ERROR_MESSAGE() AS ErrorMessage  
    ,ERROR_SEVERITY() AS ErrorSeverity  
    ,ERROR_STATE() AS ErrorState  
    ROLLBACK TRAN;  
END CATCH  
 1 //Session1:
 2 zlm@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=3 for update;
 3 Query OK, 0 rows affected (0.00 sec)
 4 
 5  ---- ---- ---- ---- 
 6 | c1 | c2 | c3 | c4 |
 7  ---- ---- ---- ---- 
 8 |  3 |  3 |  3 |  0 |
 9  ---- ---- ---- ---- 
10 1 row in set (0.00 sec)
11 
12 //Session2:
13 monitor@192.168.56.100:3306 [zlm]>begin;select * from t1 where c1=3 lock in share mode;
14 Query OK, 0 rows affected (0.00 sec)
15 
16 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
17 
18 //Session2 requested a "S" record lock on the primary key column where c1=3 while session1 has holded the "X" record lock on the same position,so session2 was blocked util lock timeout.

 

 

本文由澳门十大赌场最新排名发布于澳门十大赌场,转载请注明出处:创建_Log表及触发器,InnoDB锁冲突案例演示

关键词: mg电子游艺平台

上一篇:簡易小數處理,Server事务复制搭建与同步经验

下一篇:没有了