MySQL学习笔记（七）

游标和事务

游标cursor：

必须在声明处理程序之前被声明，并且变量和条件必须在声明游标或处理程序之前被声明

• 特性

  • 只读的，不可更新的
  • 不滚动的
  • 不敏感的，意为服务器可以或不可以复制它的结果表

声明游标

declare 游标名 cursor for select 语句

打开游标

open 游标名

从游标中提取数据

fetch 游标名 into var_name[，var_name]…

关闭游标

close 游标名

预处理SQL语句（一次创建，多次执行）
步骤
○ 创建预处理语句 prepare 预处理SQL语句名 from SQL字符串
○ 执行预处理语句 execute 预处理名[using 填充数据[,填充数据…]]
○ 释放预处理语句 deallocate prepare 预处理名

事务机制

特性（ACID）

原子性 atomicity：一个事务必须视为一个不可分割的最小工作单元，整个事务中的所有操作要么提交成功，要么全部失败回滚 一致性
consistency：数据库总数从一个一致性的状态转换到另外一个一致性的状态 隔离性
isolation：一个事务所做的修改在最终提交以前，对其它事物是不可见的 持久性
durability：一旦事务提交，则其所做的修改就会永久的保存到数据库中，不会丢失

设置隔离级别

set global transaction isolation level read uncommitted | read committed | repeatable read | serializable

• 脏读：一个事物可以读到另一个事务未提交的数据。违背了事物的隔离性原则

• 不可重复读：同一个事物内两条相同的查询语句，查询结果不一致

• 幻读：同一个事物内，两条相同的查询语句，查询的结果应该相同，但另一个事物同时提交了数据，本事务再更新时，会出现这些新数据

事务提交

• 关闭MySQL自动提交：

  显示关闭 set autocommit=0
  隐式关闭 start transaction;（不会修改系统会话变量@@autocommit的值）
  回滚：关闭自动提交后，数据库开发人员可以根据需要回滚更新操作

• 提交：

  显式提交： commit；
  隐式提交：
  a. begin、set autocommit=0、start transaction、rename table、truncate table
  b. 数据定义（create、alter、drop）语句
  c. 权限管理和账户管理语句（grant、revoke、set、password、create user、drop user、rename user等）、锁语句（lock tables、unlock tables）

保存点：可以实现事务的“部分”提交或者“部分”撤销

• 设置保存点 savepoint 保存点名;

• 回滚到保存点状态 rollback to savepoint 保存点名;

• 删除事务的保存点 release savepoint 保存点名;

锁

锁机制是实现多用户并发访问的基石。可以避免数据不一致问题的发生

锁的粒度：
指锁的作用范围。可分为服务器锁（server-level locking）和存储引擎级锁（storage-engine-level locking）

MySQL锁

• 隐式锁：MySQL自动加锁
• 显式锁：数据库开发人员手动加锁

锁类型

• 读锁（read lock）：共享锁，允许其他MySQL客户机对数据同时读，但不允许对数据任何写
• 写锁（write
  lock）：排它锁或独占锁，不允许其他MySQL客户机对数据同时读，也不允许同时写

锁钥匙：
只有对数据加锁的MySQL客户机才能够对锁进行解锁

锁的生命周期：
在同一个MySQL服务器连接内，对数据加锁到解锁之间的时间间隔

MyISAM表的表级锁：
任何针对MyISAM表的查询操作或者更新操作，都会隐式地施加表级锁
look tables命令可以同时为多个表施加表级锁

InnoDB表的行级锁：
共享锁（S）和排它锁（X）

加锁方式

• 加S锁 select * from where 条件语句 look in share mode;
• 加X锁 select * from where 条件语句 for update;
• 在更新（insert、update、delete）语句中，InnoDB存储引擎将符合更新条件的记录自动施加排它锁（隐式锁）

InnoDB表的意向锁

• 意向共享锁（IS）：向InnoDB表的某些记录施加行级共享锁时，InnoDB存储引擎会自动地向该表施加IS
• 意向排它锁（IX）：向InnoDB表的某些记录施加行级排他锁时，InnoDB存储引擎会自动地向该表施加IX