mysql的事务理解
来源:互联网 发布:拉塞尔生涯数据 编辑:程序博客网 时间:2024/05/17 00:10
1: mysql存储过程中的事务管理示例及说明
http://www.2cto.com/database/201211/170744.html
mysql存储过程中的事务管理示例及说明
今天研究了下在mysql的存储过程中使用事务的东西,现在写了一个小示例,在此记录一下,便于以后借鉴:
www.2cto.com
Sql代码
delimiter $$
use test$$
create procedure t_insert_table()
begin
/** 标记是否出错 */
declare t_error int default 0;
/** 如果出现sql异常,则将t_error设置为1后继续执行后面的操作 */
declare continue handler for sqlexception set t_error=1; -- 出错处理
/** 显式的开启事务,它开启后,事务会暂时停止自动提交*/
-- start transaction;
/** 关闭事务的自动提交 */
set autocommit = 0;
insert into t_bom_test(parent_id,child_id) values('D','abc');
insert into t_trigger_test(name,age) values('zhangsan',null);
/** 标记被改变,表示事务应该回滚 */
if t_error=1 then
rollback; -- 事务回滚
else
commit; -- 事务提交
end if;
-- rollback;
-- commit;
end$$
delimiter ;
写完这个后,又发现书本上有很多地方都是直接在开始set autocommit = 0;在最后commit或rollback就完成了,我自己试验了一下,结果出现如下问题:
1)当直接在开始set autocommit = 0;在最后commit或rollback后会出现一下错误:
Error Code: 1048. Column 'age' cannot be null ,然后查看了一下数据库表,发现第一条insert语句执行成功并成功commit了,而第二条则没有;
2)当
Sql代码
/** 标记是否出错 */
declare t_error int default 0;
/** 如果出现sql异常,则将t_error设置为1后继续执行后面的操作 */
declare continue handler for sqlexception set t_error=1; -- 出错处理
这两句代码存在,而结束时不做if-else判断,直接执行rollback语句时,发现即使两条insert语句都执行成功了,但到最后仍然会被回滚掉;当直接执行commit语句时,发现如果第一条insert语句执行成功,第二条insert语句执行失败时,查看数据库表,会发现事务没有回滚。
2:MySQL存储过程之事务管理
http://hideto.iteye.com/blog/195275
MySQL存储过程之事务管理
ACID:Atomic、Consistent、Isolated、Durable
存储程序提供了一个绝佳的机制来定义、封装和管理事务。
1,MySQL的事务支持
MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关:
隔离级别:
隔离级别决定了一个session中的事务可能对另一个session的影响、并发session对数据库的操作、一个session中所见数据的一致性
ANSI标准定义了4个隔离级别,MySQL的InnoDB都支持:
可以使用如下语句设置MySQL的session隔离级别:
MySQL默认的隔离级别是REPEATABLE READ,在设置隔离级别为READ UNCOMMITTED或SERIALIZABLE时要小心,READ UNCOMMITTED会导致数据完整性的严重问题,而SERIALIZABLE会导致性能问题并增加死锁的机率
事务管理语句:
2,定义事务
MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句,从而有效的将每条语句独立为一个事务。
在复杂的应用场景下这种方式就不能满足需求了。
为了打开事务,允许在COMMIT和ROLLBACK之前多条语句被执行,我们需要做以下两步:
1, 设置MySQL的autocommit属性为0,默认为1
2,使用START TRANSACTION语句显式的打开一个事务
如果已经打开一个事务,则SET autocommit=0不会起作用,因为START TRANSACTION会隐式的提交session中所有当前的更改,结束已有的事务,并打开一个新的事务。
使用SET AUTOCOMMIT语句的存储过程例子:
使用START TRANSACITON打开事务的例子:
通常COMMIT或ROLLBACK语句执行时才完成一个事务,但是有些DDL语句等会隐式触发COMMIT,所以应该在事务中尽可能少用或注意一下:
3,使用Savepoint
使用savepoint回滚难免有些性能消耗,一般可以用IF改写
savepoint的良好使用的场景之一是“嵌套事务”,你可能希望程序执行一个小的事务,但是不希望回滚外面更大的事务:
4,事务和锁
事务的ACID属性只能通过限制数据库的同步更改来实现,从而通过对修改数据加锁来实现。
直到事务触发COMMIT或ROLLBACK语句时锁才释放。
缺点是后面的事务必须等前面的事务完成才能开始执行,吞吐量随着等待锁释放的时间增长而递减。
MySQL/InnoDB通过行级锁来最小化锁竞争。这样修改同一table里其他行的数据没有限制,而且读数据可以始终没有等待。
可以在SELECT语句里使用FOR UPDATE或LOCK IN SHARE MODE语句来加上行级锁
FOR UPDATE会锁住该SELECT语句返回的行,其他SELECT和DML语句必须等待该SELECT语句所在的事务完成
LOCK IN SHARE MODE同FOR UPDATE,但是允许其他session的SELECT语句执行并允许获取SHARE MODE锁
死锁:
死锁发生于两个事务相互等待彼此释放锁的情景
当MySQL/InnoDB检查到死锁时,它会强制一个事务rollback并触发一条错误消息
对InnoDB而言,所选择的rollback的事务是完成工作最少的事务(所修改的行最少)
死锁在任何数据库系统里都可能发生,但是对MySQL/InnoDB这种行级锁数据库而言可能性相对较少。
可以通过使用一致的顺序来锁row或table以及让事务保持尽可能短来减少死锁的频率。
如果死锁不容易debug,你可以向你的程序中添加一些逻辑来处理死锁并重试事务,但这部分代码多了以后很难维护
所以,比较好的避免死锁的方式是在做任何修改之前按一定的顺序添加行级锁,这样就能避免死锁:
设置死锁ttl: innodb_lock_wait_timeout,默认为50秒
如果你在一个事务中混合使用InnoDB和非InnoDB表,则MySQL不能检测到死锁,此时会抛出“lock wait timeuot”1205错误
乐观所和悲观锁策略:
悲观锁:在读取数据时锁住那几行,其他对这几行的更新需要等到悲观锁结束时才能继续
乐观所:读取数据时不锁,更新时检查是否数据已经被更新过,如果是则取消当前更新
一般在悲观锁的等待时间过长而不能接受时我们才会选择乐观锁
悲观锁的例子:
乐观锁的例子:
5,事务设计指南
ACID:Atomic、Consistent、Isolated、Durable
存储程序提供了一个绝佳的机制来定义、封装和管理事务。
1,MySQL的事务支持
MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关:
- MyISAM:不支持事务,用于只读程序提高性能
- InnoDB:支持ACID事务、行级锁、并发
- Berkeley DB:支持事务
隔离级别:
隔离级别决定了一个session中的事务可能对另一个session的影响、并发session对数据库的操作、一个session中所见数据的一致性
ANSI标准定义了4个隔离级别,MySQL的InnoDB都支持:
- READ UNCOMMITTED:最低级别的隔离,通常又称为dirty read,它允许一个事务读取还没commit的数据,这样可能会提高性能,但是dirty read可能不是我们想要的
- READ COMMITTED:在一个事务中只允许已经commit的记录可见,如果session中select还在查询中,另一session此时insert一条记录,则新添加的数据不可见
- REPEATABLE READ:在一个事务开始后,其他session对数据库的修改在本事务中不可见,直到本事务commit或rollback。在一个事务中重复select的结果一样,除非本事务中update数据库。
- SERIALIZABLE:最高级别的隔离,只允许事务串行执行。为了达到此目的,数据库会锁住每行已经读取的记录,其他session不能修改数据直到前一事务结束,事务commit或取消时才释放锁。
可以使用如下语句设置MySQL的session隔离级别:
- SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
MySQL默认的隔离级别是REPEATABLE READ,在设置隔离级别为READ UNCOMMITTED或SERIALIZABLE时要小心,READ UNCOMMITTED会导致数据完整性的严重问题,而SERIALIZABLE会导致性能问题并增加死锁的机率
事务管理语句:
- START TRANSACTION:开始事务,autocommit设为0,如果已经有一个事务在运行,则会触发一个隐藏的COMMIT
- COMMIT:提交事务,保存更改,释放锁
- ROLLBACK:回滚本事务对数据库的所有更改,然后结束事务,释放锁
- SAVEPOINT savepoint_name:创建一个savepoint识别符来ROLLBACK TO SAVEPOINT
- ROLLBACK TO SAVEPOINT savepoint_name:回滚到从savepoint_name开始对数据库的所有更改,这样就允许回滚事务中的一部分,保证更改的一个子集被提交
- SET TRANSACTION:允许设置事务的隔离级别
- LOCK TABLES:允许显式的锁住一个或多个table,会隐式的关闭当前打开的事务,建议在执行LOCK TABLES语句之前显式的commit或rollback。我们一般所以一般在事务代码里不会使用LOCK TABLES
2,定义事务
MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句,从而有效的将每条语句独立为一个事务。
在复杂的应用场景下这种方式就不能满足需求了。
为了打开事务,允许在COMMIT和ROLLBACK之前多条语句被执行,我们需要做以下两步:
1, 设置MySQL的autocommit属性为0,默认为1
2,使用START TRANSACTION语句显式的打开一个事务
如果已经打开一个事务,则SET autocommit=0不会起作用,因为START TRANSACTION会隐式的提交session中所有当前的更改,结束已有的事务,并打开一个新的事务。
使用SET AUTOCOMMIT语句的存储过程例子:
- CREATE PROCEDURE tfer_funds
- (from_account int, to_account int, tfer_amount numeric(10,2))
- BEGIN
- SET autocommit=0;
- UPDATE account_balance SET balance=balance-tfer_amount WHERE account_id=from_account;
- UPDATE account_balance SET balance=balance+tfer_amount WHERE account_id=to_account;
- COMMIT;
- END;
使用START TRANSACITON打开事务的例子:
- CREATE PROCEDURE tfer_funds
- (from_account int, to_account int, tfer_amount numeric(10,2))
- BEGIN
- START TRANSACTION;
- UPDATE account_balance SET balance=balance-tfer_amount WHERE account_id=from_account;
- UPDATE account_balance SET balance=balance+tfer_amount WHERE account_id=to_account;
- COMMIT;
- END;
通常COMMIT或ROLLBACK语句执行时才完成一个事务,但是有些DDL语句等会隐式触发COMMIT,所以应该在事务中尽可能少用或注意一下:
- ALTER FUNCTION
- ALTER PROCEDURE
- ALTER TABLE
- BEGIN
- CREATE DATABASE
- CREATE FUNCTION
- CREATE INDEX
- CREATE PROCEDURE
- CREATE TABLE
- DROP DATABASE
- DROP FUNCTION
- DROP INDEX
- DROP PROCEDURE
- DROP TABLE
- UNLOCK TABLES
- LOAD MASTER DATA
- LOCK TABLES
- RENAME TABLE
- TRUNCATE TABLE
- SET AUTOCOMMIT=1
- START TRANSACTION
3,使用Savepoint
使用savepoint回滚难免有些性能消耗,一般可以用IF改写
savepoint的良好使用的场景之一是“嵌套事务”,你可能希望程序执行一个小的事务,但是不希望回滚外面更大的事务:
- CREATE PROCEDURE nested_tfer_funds
- (in_from_acct INTEGER,
- in_to_acct INTEGER,
- in_tfer_amount DECIMAL(8,2))
- BEGIN
- DECLARE txn_error INTEGER DEFAULT 0;
- DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
- SET txn_error=1;
- END
- SAVEPINT savepint_tfer;
- UPDATE account_balance
- SET balance=balance-in_tfer_amount
- WHERE account_id=in_from_acct;
- IF txn_error THEN
- ROLLBACK TO savepoint_tfer;
- SELECT 'Transfer aborted';
- ELSE
- UPDATE account_balance
- SET balance=balance+in_tfer_amount
- WHERE account_id=in_to_acct;
- IF txn_error THEN
- ROLLBACK TO savepoint_tfer;
- SELECT 'Transfer aborted';
- END IF:
- END IF;
- END;
4,事务和锁
事务的ACID属性只能通过限制数据库的同步更改来实现,从而通过对修改数据加锁来实现。
直到事务触发COMMIT或ROLLBACK语句时锁才释放。
缺点是后面的事务必须等前面的事务完成才能开始执行,吞吐量随着等待锁释放的时间增长而递减。
MySQL/InnoDB通过行级锁来最小化锁竞争。这样修改同一table里其他行的数据没有限制,而且读数据可以始终没有等待。
可以在SELECT语句里使用FOR UPDATE或LOCK IN SHARE MODE语句来加上行级锁
- SELECT select_statement options [FOR UPDATE|LOCK IN SHARE MODE]
FOR UPDATE会锁住该SELECT语句返回的行,其他SELECT和DML语句必须等待该SELECT语句所在的事务完成
LOCK IN SHARE MODE同FOR UPDATE,但是允许其他session的SELECT语句执行并允许获取SHARE MODE锁
死锁:
死锁发生于两个事务相互等待彼此释放锁的情景
当MySQL/InnoDB检查到死锁时,它会强制一个事务rollback并触发一条错误消息
对InnoDB而言,所选择的rollback的事务是完成工作最少的事务(所修改的行最少)
- mysql > CALL tfer_funds(1,2,300);
- ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
死锁在任何数据库系统里都可能发生,但是对MySQL/InnoDB这种行级锁数据库而言可能性相对较少。
可以通过使用一致的顺序来锁row或table以及让事务保持尽可能短来减少死锁的频率。
如果死锁不容易debug,你可以向你的程序中添加一些逻辑来处理死锁并重试事务,但这部分代码多了以后很难维护
所以,比较好的避免死锁的方式是在做任何修改之前按一定的顺序添加行级锁,这样就能避免死锁:
- CREATE PROCEDURE tfer_funds3
- (from_account INT, to_account INT, tfer_amount NUMERIC(10,2))
- BEGIN
- DECLARE local_account_id INT;
- DECLARE lock_cursor CURSOR FOR
- SELECT account_id
- FROM account_balance
- WHERE account_id IN (from_account, to_account)
- ORDER BY account_id
- FOR UPDATE;
- START TRANSACTION;
- OPEN lock_cursor;
- FETCH lock_cursor INTO local_account_id;
- UPDATE account_balance
- SET balance=balance-tfer_amount
- WHERE account_id=from_account;
- UPDATE account_balance
- SET balance=balance+tfer_amount
- WHERE account_id=to_account;
- CLOSE lock_cursor;
- COMMIT;
- END;
设置死锁ttl: innodb_lock_wait_timeout,默认为50秒
如果你在一个事务中混合使用InnoDB和非InnoDB表,则MySQL不能检测到死锁,此时会抛出“lock wait timeuot”1205错误
乐观所和悲观锁策略:
悲观锁:在读取数据时锁住那几行,其他对这几行的更新需要等到悲观锁结束时才能继续
乐观所:读取数据时不锁,更新时检查是否数据已经被更新过,如果是则取消当前更新
一般在悲观锁的等待时间过长而不能接受时我们才会选择乐观锁
悲观锁的例子:
- CREATE PROCEDURE tfer_funds
- (from_account INT, to_account INT,tfer_amount NUMERIC(10,2),
- OUT status INT, OUT message VARCHAR(30))
- BEGIN
- DECLARE from_account_balance NUMERIC(10,2);
- START TRANSACTION;
- SELECT balance
- INTO from_account_balance
- FROM account_balance
- WHERE account_id=from_account
- FOR UPDATE;
- IF from_account_balance>=tfer_amount THEN
- UPDATE account_balance
- SET balance=balance-tfer_amount
- WHERE account_id=from_account;
- UPDATE account_balance
- SET balance=balance+tfer_amount
- WHERE account_id=to_account;
- COMMIT;
- SET status=0;
- SET message='OK';
- ELSE
- ROLLBACK;
- SET status=-1;
- SET message='Insufficient funds';
- END IF;
- END;
乐观锁的例子:
- CREATE PROCEDURE tfer_funds
- (from_account INT, to_account INT, tfer_amount NUMERIC(10,2),
- OUT status INT, OUT message VARCHAR(30) )
- BEGIN
- DECLARE from_account_balance NUMERIC(8,2);
- DECLARE from_account_balance2 NUMERIC(8,2);
- DECLARE from_account_timestamp1 TIMESTAMP;
- DECLARE from_account_timestamp2 TIMESTAMP;
- SELECT account_timestamp,balance
- INTO from_account_timestamp1,from_account_balance
- FROM account_balance
- WHERE account_id=from_account;
- IF (from_account_balance>=tfer_amount) THEN
- -- Here we perform some long running validation that
- -- might take a few minutes */
- CALL long_running_validation(from_account);
- START TRANSACTION;
- -- Make sure the account row has not been updated since
- -- our initial check
- SELECT account_timestamp, balance
- INTO from_account_timestamp2,from_account_balance2
- FROM account_balance
- WHERE account_id=from_account
- FOR UPDATE;
- IF (from_account_timestamp1 <> from_account_timestamp2 OR
- from_account_balance <> from_account_balance2) THEN
- ROLLBACK;
- SET status=-1;
- SET message=CONCAT("Transaction cancelled due to concurrent update",
- " of account" ,from_account);
- ELSE
- UPDATE account_balance
- SET balance=balance-tfer_amount
- WHERE account_id=from_account;
- UPDATE account_balance
- SET balance=balance+tfer_amount
- WHERE account_id=to_account;
- COMMIT;
- SET status=0;
- SET message="OK";
- END IF;
- ELSE
- ROLLBACK;
- SET status=-1;
- SET message="Insufficient funds";
- END IF;
- END$$
5,事务设计指南
- 1,保持事务短小
- 2,尽量避免事务中rollback
- 3,尽量避免savepoint
- 4,默认情况下,依赖于悲观锁
- 5,为吞吐量要求苛刻的事务考虑乐观锁
- 6,显示声明打开事务
- 7,锁的行越少越好,锁的时间越短越好
- mysql的事务理解
- mysql事务的理解
- mysql中事务的理解
- mysql事务的简单理解
- Mysql事务及行级锁的理解
- Mysql事务及行级锁的理解
- Mysql事务及行级锁的理解
- Mysql事务及行级锁的理解
- Mysql事务及行级锁的理解
- MySQL事务个人理解
- Spring TransactionTemplate + Mysql事务理解
- Mysql中事务隔离级别与binlog_format的一点理解
- 通过例子理解mysql的事务隔离级别
- java事务大总结(一) 先理解数据库的事务以mysql为例
- 理解mysql数据库的事务特征,事务隔离级别,加锁机制
- 事务的理解
- sql事务的理解
- 事务的理解
- 【木头Cocos2d-x 029】Lua篇(第04章):来点高难度的,获取Lua表结构数据
- oracle null和空字符串
- Java高新技术之银行业务调度系统(java单例设计模式、java枚举)
- 局部变量
- Linux 图形系统之 —— X Window
- mysql的事务理解
- 隐藏console窗口方法
- mysql的utf8中文排序的解决方法
- jpivot 展示的一些技巧
- 窗口样式
- 数据结构---->图的存储结构
- MySQL5的异常处理
- google maps V3 给多个marker添加infowindows信息
- 在C++文件读写操作中,seekg()和seekp()是一样的功能