数据库存储过程和锁机制

存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的，存储过程思想上很简单,就是数据库SQL语言层面的代码封装与有重用

特点

封装,复用,可以接收参数,也可以返回数据,减少网络交互,效率提升

基本语法

创建存储过程：
create procedure p1()
begin
select count(*)from student;
end;

调用：
call p1();

查看存储过程：
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';

删除：
drop procedure if exists p1;

锁机制

分类

1. 全局锁:锁定数据库中的所有表。

2. 表级锁:每次操作锁住整张表。

3. 行级锁：每次操作锁住对应的行数据。

全局锁

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

案例

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性，但是可以执行dql语句。

指令

flush tables with read lock.   //加全局锁
mysqldump -uroot -p1234 itcast >itcast.sql   //数据库备份
unlock tables.  //释放全局锁

表级锁

每次操作锁住整张表，锁定粒度大，发生锁冲突的概率最高，并发度最低

表锁

a.表共享读锁(read lock)

创建表的读锁以后，所有客户端一样只能进行查询操作，其他修改DML和DDL语句都无法执行

b.表独占写锁(write lock)

客户端一创建写锁以后，对表数据既可以读也可以执行其他的操作，但是是独占的形式，其他客户端都会进入阻塞的状态

元数据锁(meta data lock，MDL)

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上，MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁(共享);当对表结构进行变更操作的时候，加MDL写锁(排他)。

意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查，如下实例

注意：为什么引入意向锁，因为在表数据量非常大的情况下，如果我们增加表锁，逐行检查带来消耗太大，使用意向锁我们在增加表锁的时候直接可以判断两个锁是否兼容实现锁的加入。

a.意向共享锁(IS)

由语句 select...lock in share mode添加，与表锁共享锁(read)兼容，与表锁排它锁(write)互斥

b. 意向排他锁(IX)

出insert、update、delete、select...for update 添加，与表锁共享锁(read)及排它锁(write)都互斥。意向锁之间不会互斥。

行级锁

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高，应用在InnoDB存储引擎中;InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁，

1.行锁(Record Lock)

锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。

a.共享锁

允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。

b.排他锁

允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

c.两者的兼容关系

d.sql的默认锁

思考： 在索引当时我们说过在一条数据执行updata的情况下，如果我们查询条件后是没有加索引的，我们会默认升级行锁变成表锁！也正是这个原因-----InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁，

2.间隙锁(Gap Lock):

锁定索引记录间隙(不含该记录)确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

3.临键锁(Next-Key Lock):

行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

默认情况下，InnoDB在读事务隔离级别运行，InnoDB使用次密钥锁进行搜索和索引扫描，以防止幻读。

1.索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁。

2.索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，次键锁退化为间隙锁。

3.索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。