`
wwty
  • 浏览: 543023 次
  • 性别: Icon_minigender_1
  • 来自: 北京-郑州
社区版块
存档分类
最新评论

mysql存储过程之事务篇

阅读更多

事务的四大特征:

ACID:Atomic(原子性)、Consistent(一致性)、Isolated(独立性)、Durable (持久性)

 

MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关: 

sql代码 

1. MyISAM:不支持事务,用于只读程序提高性能  

2. InnoDB:支持ACID事务、行级锁、并发  

3. Berkeley DB:支持事务  

 

事务隔离级别标准:

ANSI美国国家标准学会)标准定义了4个隔离级别,MySQLInnoDB都支持: 

sql代码 

1. READ UNCOMMITTED:最低级别的隔离,通常又称为dirty read,它允许一个事务读取还没commit的数据,这样可能会提高性能,但是dirty read可能不是我们想要的  

2. READ COMMITTED:在一个事务中只允许已经commit的记录可见,如果sessionselect还在查询中,另一session此时insert一条记录,则新添加的数据不可见  

3. REPEATABLE READ:在一个事务开始后,其他session对数据库的修改在本事务中不可见,直到本事务commitrollback。在一个事务中重复select的结果一样,除非本事务中update数据库。

4. SERIALIZABLE:最高级别的隔离,只允许事务串行执行。为了达到此目的,数据库会锁住每行已经读取的记录,其他session不能修改数据直到前一事务结束,事务commit或取消时才释放锁。  

Mysql的默认隔离级别是:REPEATABLE READ

READ UNCOMMITTED级别会导致数据完整性的严重问题,需要自己控制如何保持数据完整性

SERIALIZABLE会导致性能问题并增加死锁的机率

 

Mysql事务操作语句:

1.  START TRANSACTION:开始事务,autocommit设为0如果已经有一个事务在运行,则会触发一个隐藏的COMMIT  

2.  COMMIT:提交事务,保存更改,释放锁  

3.  ROLLBACK:回滚本事务对数据库的所有更改,然后结束事务,释放锁  

4.  SAVEPOINT savepoint_name:创建一个savepoint识别符来ROLLBACK TO SAVEPOINT  

5.  ROLLBACK TO SAVEPOINT savepoint_name:回滚到从savepoint_name开始对数据库的所有更改,这样就允许回滚事务中的一部分,保证更改的一个子集被提交  

6.  SET TRANSACTION:允许设置事务的隔离级别  

7.  LOCK TABLES:允许显式的锁住一个或多个table,会隐式的关闭当前打开的事务,建议在执行LOCK TABLES语句之前显式的commitrollback。我们一般所以一般在事务代码里不会使用LOCK TABLES  

 

定义事务

MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句,从而有效的将每条语句独立为一个事务。 
在复杂的应用场景下这种方式就不能满足需求了。 
为了打开事务,允许在COMMITROLLBACK之前多条语句被执行,我们需要做以下两步: 
1, 设置MySQLautocommit属性为0,默认为
2,使用START TRANSACTION语句显式的打开一个事务

上面已经说了,当使用START TRANSACTION开始一个事物的时候,则SET autocommit=0不会起作用,因为START TRANSACTION会隐式的提交session中所有当前的更改,结束已有的事务,并打开一个新的事务。

 

 

使用SET AUTOCOMMIT语句的存储过程例子: 

sql代码 

1. CREATE PROCEDURE tfer_funds  

2.     (from_account int, to_account int, tfer_amount numeric(10,2))  

3. BEGIN  

4.     SET autocommit=0;  

5.   

6.     UPDATE account_balance SET balance=balance-tfer_amount WHERE account_id=from_account;  

7.   

8.     UPDATE account_balance SET balance=balance+tfer_amount WHERE account_id=to_account;  

9.   

10.    COMMIT;  

11.END;  


使用START TRANSACITON打开事务的例子: 

sql代码 

1. CREATE PROCEDURE tfer_funds  

2.     (from_account int, to_account int, tfer_amount numeric(10,2))  

3. BEGIN  

4.     START TRANSACTION;  

5.   

6.     UPDATE account_balance SET balance=balance-tfer_amount WHERE account_id=from_account;  

7.   

8.     UPDATE account_balance SET balance=balance+tfer_amount WHERE account_id=to_account;  

9.   

10.    COMMIT;  

11.END;  

 

 

通常COMMITROLLBACK语句执行时才完成一个事务,但是有些DDL语句等会隐式触发COMMIT,所以应该在事务中尽可能少用或注意一下: 

Java代码 

1. ALTER FUNCTION  

2. ALTER PROCEDURE  

3. ALTER TABLE  

4. BEGIN  

5. CREATE DATABASE  

6. CREATE FUNCTION  

7. CREATE INDEX  

8. CREATE PROCEDURE  

9. CREATE TABLE  

10.DROP DATABASE  

11.DROP FUNCTION  

12.DROP INDEX  

13.DROP PROCEDURE  

14.DROP TABLE  

15.UNLOCK TABLES  

16.LOAD MASTER DATA  

17.LOCK TABLES  

18.RENAME TABLE  

19.TRUNCATE TABLE  

20.SET AUTOCOMMIT=1  

21.START TRANSACTION  

 

关于savepoint当前先不做学习

 

事务和锁

事务的ACID属性只能通过限制数据库的同步更改来实现,通过对数据加锁来实现。 
直到事务触发COMMITROLLBACK语句时锁才释放。
这样做的缺点是后面的事务必须等前面的事务完成才能开始执行,吞吐量随着等待锁释放的时间增长而递减。

 

Mysqlinnodb通过行级锁来最小化锁竞争。这样修改同一table里其他行的数据没有限制,而且读数据可以始终没有等待。

 

可以在SELECT语句里使用FOR UPDATELOCK IN SHARE MODE语句来加上行级锁 

1. SELECT select_statement options [FOR UPDATE|LOCK IN SHARE MODE]  

 

FOR UPDATE会锁住该SELECT语句返回的行,其他SELECTDML语句必须等待该SELECT语句所在的事务完成 
LOCK IN SHARE MODEFOR UPDATE,但是允许其他sessionSELECT语句执行并允许获取SHARE MODE 

 

 

下面了解一下死锁,悲观锁,乐观锁,但是不深入掌握,当前只掌握概念

死锁发生于两个事务相互等待彼此释放锁的情景
悲观锁:在读取数据时锁住那几行,其他对这几行的更新需要等到悲观锁结束时才能继续
乐观所:读取数据时不锁,更新时检查是否数据已经被更新过,如果是则取消当前更新

一般在悲观锁的等待时间过长而不能接受时我们才会选择乐观锁

事务设计指南

1,保持事务短小  

2,尽量避免事务中rollback  

3,尽量避免savepoint  

4,默认情况下,依赖于悲观锁  

5,为吞吐量要求苛刻的事务考虑乐观锁  

6,显示声明打开事务  

7,锁的行越少越好,锁的时间越短越好

 

 

分享到:
评论

相关推荐

    MySql存储过程与事务处理教学PPT

    什么情况适合用存储过程? 当多个用不同语言开发的应用程序或不同平台的应用程序需要去执行相同的数据库操作. (避免为各个程序都开发相同的功能) 安全性要求较高时,使用存储程序和...MySql存储过程与事务处理教学PPT

    mysql经典教程+mysql存储过程讲解

    本教程结合"mysql经典教程+mysql存储过程讲解"的主题,将深入探讨MySQL的基础知识以及核心特性——存储过程。 首先,我们需要理解什么是数据库。数据库是一个组织和存储数据的系统,允许用户以结构化方式访问和管理...

    MySQL存储过程学习

    MySQL存储过程是数据库管理系统中的一种重要功能,它允许开发者预编译一系列的SQL语句,形成一个可重复使用的代码块,以提高数据处理的效率和应用程序的性能。在这个"MySQL存储过程学习"的主题中,我们将深入探讨...

    MySQL存储过程.rar

    在实际应用中,MySQL存储过程广泛用于实现数据库的事务处理、数据备份和恢复、业务规则的封装等场景。例如,可以创建一个存储过程来批量插入数据,或者在一组操作失败时自动回滚事务,保证数据一致性。 在提供的...

    MySQL存储过程编程教程.pdf

    MySQL存储过程编程是数据库应用开发中的一个重要环节,它可以帮助开发者将一系列的数据库操作封装起来,以提高程序的可维护性和运行效率。在MySQL 5.0及以上版本中,存储过程、函数和触发器的支持为数据库提供了更...

    存储过程文档--mysql

    存储过程文档--MySQL 存储过程是 MySQL 中的一个强大功能,它允许用户预先将常用的或复杂的工作写入 SQL 语句,并将其存储起来,以便在以后的数据库操作中可以快速调用和执行。存储过程可以提高数据库的执行速度,...

    MySQL存储过程.pdf

    MySQL存储过程是数据库管理系统中的一种重要功能,它允许开发者预编译一组SQL语句并封装成一个可重复使用的对象。这种技术在处理复杂的业务逻辑、提高数据操作效率以及减少网络传输时非常有用。本文件“MySQL存储...

    MySQL存储过程经典教程

    MySQL存储过程是数据库管理系统中的一种重要功能,它允许开发者预编译一组SQL语句并封装成一个可重复使用的对象。这个经典教程旨在深入探讨存储过程的各个方面,帮助读者掌握这一强大的数据库编程工具。 1. **存储...

    MySQL存储过程基础教程.pdf

    ### MySQL存储过程基础知识点 #### 1. 存储过程的定义和示例 存储过程是存储在MySQL服务器上的预编译的SQL代码段,它能够接受参数、执行一系列的SQL语句和流程控制语句。存储过程可以提高数据库操作的效率,同时...

    Mysql存储过程和函数

    ### MySQL存储过程与函数详解 #### 一、概述 MySQL是一种广泛使用的开源关系型数据库管理系统,在数据管理和存储方面提供了强大的支持。其中,存储过程和函数是MySQL提供的两种重要的编程特性,它们可以帮助开发者...

    精通MySQL存储过程和函数

    ### 精通MySQL存储过程和函数 #### 1. 说明 ##### 1.1 手册适用范围 本手册适用于对MySQL存储过程和函数感兴趣的开发者和技术人员,特别是那些希望深入了解并掌握如何在实际项目中高效利用这些功能的专业人士。...

    php中调用MySQL存储过程

    ### PHP中调用MySQL存储过程 #### 背景与目的 在Web开发中,PHP是一种广泛使用的服务器端脚本语言,它与MySQL数据库结合得非常紧密,为开发者提供了强大的功能来处理数据。存储过程是预编译好的SQL代码块,它们...

    MySQL SQL高级特性-存储过程-触发器-事务

    MySQL是目前最流行的开源关系型数据库管理系统之一,它提供了...在本文中,我们学习了MySQL存储过程的定义和优点,触发器的概念,以及事务的基本原理。通过这些高级特性,我们可以开发出更稳定、高效的数据库应用程序。

    mysql存储过程电子书,可以参考

    MySQL存储过程是数据库管理系统中的一种重要功能,它允许开发者预定义一组SQL语句,并将其封装成一个可重复使用的单元。这个电子书显然专注于探讨MySQL存储过程的各个方面,为学习者提供了一个深入理解、创建和使用...

    PHP和MySQL存储过程实例

    根据给定的信息,我们可以深入探讨PHP与MySQL存储过程的相关知识点,包括如何在MySQL中使用`CONCAT`函数、创建存储过程以及如何通过PHP脚本来调用这些存储过程。 ### 使用 CONCAT 函数 #### 标题中的示例 ```sql ...

    MySQL实现创建存储过程并循环添加记录的方法

    在MySQL数据库中,存储过程是一种预编译的SQL语句集合,它可以封装一系列的操作,用于执行复杂的业务逻辑。创建存储过程并循环添加记录是数据库管理中的常见任务,尤其是在需要批量插入数据时。以下将详细解释如何在...

    MySQL存储过程

    在实际应用中,MySQL存储过程广泛应用于业务逻辑处理、数据批量处理、事务操作等场景,尤其在需要重复执行相同或类似操作时,存储过程的优势尤为明显。通过合理地使用存储过程,开发人员可以提高代码的可维护性,...

Global site tag (gtag.js) - Google Analytics