大气网站图,云南seo整站优化报价,wordpress icp备案,百度官方网站网址是多少MySQL存储过程之事务管理 ACID:Atomic、Consistent、Isolated、Durable 存储程序提供了一个绝佳的机制来定义、封装和管理事务。 1#xff0c;MySQL的事务支持 MySQL的事务支持不是绑定在MySQL服务器本身#xff0c;而是与存储引擎相关#xff1a; Java代码 MyISAM#xff…MySQL存储过程之事务管理 ACID:Atomic、Consistent、Isolated、Durable 存储程序提供了一个绝佳的机制来定义、封装和管理事务。 1MySQL的事务支持 MySQL的事务支持不是绑定在MySQL服务器本身而是与存储引擎相关 Java代码 MyISAM不支持事务用于只读程序提高性能 InnoDB支持ACID事务、行级锁、并发 Berkeley DB支持事务 隔离级别 隔离级别决定了一个session中的事务可能对另一个session的影响、并发session对数据库的操作、一个session中所见数据的一致性 ANSI标准定义了4个隔离级别MySQL的InnoDB都支持 Java代码 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隔离级别 Java代码 SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} MySQL默认的隔离级别是REPEATABLE READ在设置隔离级别为READ UNCOMMITTED或SERIALIZABLE时要小心READ UNCOMMITTED会导致数据完整性的严重问题而SERIALIZABLE会导致性能问题并增加死锁的机率 事务管理语句 Java代码 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 autocommit0不会起作用因为START TRANSACTION会隐式的提交session中所有当前的更改结束已有的事务并打开一个新的事务。 使用SET AUTOCOMMIT语句的存储过程例子 Java代码 CREATE PROCEDURE tfer_funds (from_account int, to_account int, tfer_amount numeric(10,2)) BEGIN SET autocommit0; UPDATE account_balance SET balancebalance-tfer_amount WHERE account_idfrom_account; UPDATE account_balance SET balancebalancetfer_amount WHERE account_idto_account; COMMIT; END; 使用START TRANSACITON打开事务的例子 Java代码 CREATE PROCEDURE tfer_funds (from_account int, to_account int, tfer_amount numeric(10,2)) BEGIN START TRANSACTION; UPDATE account_balance SET balancebalance-tfer_amount WHERE account_idfrom_account; UPDATE account_balance SET balancebalancetfer_amount WHERE account_idto_account; COMMIT; END; 通常COMMIT或ROLLBACK语句执行时才完成一个事务但是有些DDL语句等会隐式触发COMMIT所以应该在事务中尽可能少用或注意一下 Java代码 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 AUTOCOMMIT1 START TRANSACTION 3使用Savepoint 使用savepoint回滚难免有些性能消耗一般可以用IF改写 savepoint的良好使用的场景之一是“嵌套事务”你可能希望程序执行一个小的事务但是不希望回滚外面更大的事务 Java代码 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_error1; END SAVEPINT savepint_tfer; UPDATE account_balance SET balancebalance-in_tfer_amount WHERE account_idin_from_acct; IF txn_error THEN ROLLBACK TO savepoint_tfer; SELECT Transfer aborted; ELSE UPDATE account_balance SET balancebalancein_tfer_amount WHERE account_idin_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语句来加上行级锁 Java代码 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的事务是完成工作最少的事务所修改的行最少 Java代码 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你可以向你的程序中添加一些逻辑来处理死锁并重试事务但这部分代码多了以后很难维护 所以比较好的避免死锁的方式是在做任何修改之前按一定的顺序添加行级锁这样就能避免死锁: Java代码 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 balancebalance-tfer_amount WHERE account_idfrom_account; UPDATE account_balance SET balancebalancetfer_amount WHERE account_idto_account; CLOSE lock_cursor; COMMIT; END; 设置死锁ttl: innodb_lock_wait_timeout默认为50秒 如果你在一个事务中混合使用InnoDB和非InnoDB表则MySQL不能检测到死锁此时会抛出“lock wait timeuot”1205错误 乐观所和悲观锁策略 悲观锁在读取数据时锁住那几行其他对这几行的更新需要等到悲观锁结束时才能继续 乐观所读取数据时不锁更新时检查是否数据已经被更新过如果是则取消当前更新 一般在悲观锁的等待时间过长而不能接受时我们才会选择乐观锁 悲观锁的例子 Java代码 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_idfrom_account FOR UPDATE; IF from_account_balancetfer_amount THEN UPDATE account_balance SET balancebalance-tfer_amount WHERE account_idfrom_account; UPDATE account_balance SET balancebalancetfer_amount WHERE account_idto_account; COMMIT; SET status0; SET messageOK; ELSE ROLLBACK; SET status-1; SET messageInsufficient funds; END IF; END; 乐观锁的例子 Java代码 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_idfrom_account; IF (from_account_balancetfer_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_idfrom_account FOR UPDATE; IF (from_account_timestamp1 from_account_timestamp2 OR from_account_balance from_account_balance2) THEN ROLLBACK; SET status-1; SET messageCONCAT(Transaction cancelled due to concurrent update, of account ,from_account); ELSE UPDATE account_balance SET balancebalance-tfer_amount WHERE account_idfrom_account; UPDATE account_balance SET balancebalancetfer_amount WHERE account_idto_account; COMMIT; SET status0; SET messageOK; END IF; ELSE ROLLBACK; SET status-1; SET messageInsufficient funds; END IF; END$$ 5事务设计指南 Java代码 1保持事务短小 2尽量避免事务中rollback 3尽量避免savepoint 4默认情况下依赖于悲观锁 5为吞吐量要求苛刻的事务考虑乐观锁 6显示声明打开事务 7锁的行越少越好锁的时间越短越好 转载于:https://www.cnblogs.com/sunwei2012/archive/2010/12/14/1905436.html
