`
xiaoshenge
  • 浏览: 169794 次
  • 性别: Icon_minigender_1
  • 来自: 十堰
社区版块
存档分类
最新评论

msyql的事务和行锁的应用

 
阅读更多

项目情景:游戏推广网站,登录登录用户可以领取一次游戏礼包(就是一个兑换码)。兑换码是事先插入到数据库的,然后用户点击领取后,从数据库中取出一条没有领取过的,然后把此条记录update一下更新为领取。此处考虑到,如果多个用户同时点击领取的时候,有可能查询出来的兑换码是同一条,然后问题就来了。

解决方法一:

 

 

try{
BEGIN;

SELECT FId, FCode FROM Tbl_Code WHERE  FStatus=0 limit 1;


UPDATE Tbl_Code SET FStatus=1 WHERE  FId = '' AND FStatus=0

COMMIT;
} catch (exception $e){

rollback;


}

 后来考虑到mysql的inodedb还有行锁,为了确保问题可以利用行锁:

 

try{
BEGIN;

SELECT FId, FCode FROM Tbl_Code WHERE  FStatus=0 limit 1 for update;


UPDATE Tbl_Code SET FStatus=1 WHERE  FId = '' AND FStatus=0

COMMIT;
} catch (exception $e){

rollback;


}

 

此种方法参考了:MySQL与事务 

http://hi.baidu.com/thinkinginlamp/blog/item/d677cffcb7098482b901a014.html

 

 

作者:老王

MySQL5.X都已经发布好久了,但是还有很多人认为MySQL是不支持事务处理的,这不得不怪他们是孤陋寡闻的,其实,只要你的MySQL版本支持BDB或InnoDB表类型,那么你的MySQL就具有事务处理的能力。这里面,又以InnoDB表类型用的最多,虽然后来发生了诸如Oracle收购InnoDB等令MySQL不爽的事情,但那些商业上的斗争与技术无关,下面以InnoDB表类型为例简单说一下MySQL中的事务。

先来明确一下事务涉及的相关知识:

事务都应该具备ACID特征。所谓ACID是Atomic(原子性),Consistent(一致性),Isolated(隔离性),Durable(持续性)四个词的首字母所写,下面以“银行转帐”为例来分别说明一下它们的含义:

原子性:组成事务处理的语句形成了一个逻辑单元,不能只执行其中的一部分。换句话说,事务是不可分割的最小单元。比如:银行转帐过程中,必须同时从一个帐户减去转帐金额,并加到另一个帐户中,只改变一个帐户是不合理的。

一致性:在事务处理执行前后,数据库是一致的。也就是说,事务应该正确的转换系统状态。比如:银行转帐过程中,要么转帐金额从一个帐户转入另一个帐户,要么两个帐户都不变,没有其他的情况。

隔离性:一个事务处理对另一个事务处理没有影响。就是说任何事务都不可能看到一个处在不完整状态下的事务。比如说,银行转帐过程中,在转帐事务没有提交之前,另一个转帐事务只能处于等待状态。

持续性:事务处理的效果能够被永久保存下来。反过来说,事务应当能够承受所有的失败,包括服务器、进程、通信以及媒体失败等等。比如:银行转帐过程中,转帐后帐户的状态要能被保存下来。

再来看看哪些问题会用到事务处理:

 

这里不说“银行转帐”的例子了,说一个大家实际更容易遇到的“网上购书”的例子。先假设一下问题的背景:网上购书,某书(数据库编号为123)只剩最后一本,而这个时候,两个用户对这本书几乎同时发出了购买请求,让我们看看整个过程:

在具体分析之前,先来看看数据表的定义:

-------------------------------------------------------------------------------

create table book
(
    book_id unsigned int(10) not null auto_increment,
    book_name varchar(100) not null,
    book_price float(5, 2) not null, #我假设每本书的价格不会超过999.99元
    book_number int(10) not null,
    primary key (book_id)
)
type = innodb; #engine = innodb也行

-------------------------------------------------------------------------------

对于用户甲来说,他的动作稍微比乙快一点点,其购买过程所触发的动作大致是这样的:

-------------------------------------------------------------------------------

1. SELECT book_number FROM book WHERE  book_id = 123;

book_number大于零,确认购买行为并更新book_number

2. UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

购书成功

-------------------------------------------------------------------------------

而对于用户乙来说,他的动作稍微比甲慢一点点,其购买过程所触发的动作和甲相同:

-------------------------------------------------------------------------------

1. SELECT book_number FROM book WHERE  book_id = 123;

这个时候,甲刚刚进行完第一步的操作,还没来得及做第二步操作,所以book_number一定大于零

2. UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

购书成功

-------------------------------------------------------------------------------

表面上看甲乙的操作都成功了,他们都买到了书,但是库存只有一本,他们怎么可能都成功呢?再看看数据表里book_number的内容,已经变成“-1”了,这当然是不能允许的(实际上,声明这样的列类型应该加上unsigned的属性,以保证其不能为负,这里是为了说明问题所以没有这样设置)

好了,问题陈述清楚了,再来看看怎么利用事务来解决这个问题,打开MySQL手册,可以看到想用事务来保护你的SQL正确执行其实很简单,基本就是三个语句:开始,提交,回滚。

-------------------------------------------------------------------------------

开始:START TRANSACTION或BEGIN语句可以开始一项新的事务

提交:COMMIT可以提交当前事务,是变更成为永久变更

回滚:ROLLBACK可以回滚当前事务,取消其变更

此外,SET AUTOCOMMIT = {0 | 1}可以禁用或启用默认的autocommit模式,用于当前连接。

-------------------------------------------------------------------------------

那是不是只要用事务语句包一下我们的SQL语句就能保证正确了呢?比如下面代码:

-------------------------------------------------------------------------------

BEGIN;

SELECT book_number FROM book WHERE  book_id = 123;

// ...

UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

COMMIT;

-------------------------------------------------------------------------------

答案是否定了,这样依然不能避免问题的发生,如果想避免这样的情况,实际应该如下:

-------------------------------------------------------------------------------

BEGIN;

SELECT book_number FROM book WHERE  book_id = 123 FOR UPDATE;

// ...

UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

COMMIT;

-------------------------------------------------------------------------------

由于加入了FOR UPDATE,所以会在此条记录上加上一个行锁,如果此事务没有完全结束,那么其他的事务在使用SELECT ... FOR UPDATE请求的时候就会处于等待状态,直到上一个事务结束,它才能继续,从而避免了问题的发生,需要注意的是,如果你其他的事务使用的是不带FOR UPDATE的SELECT语句,将得不到这种保护。

最后看看PHP + MySQL事务操作的代码演示:

实际LAMP应用中,一般PHP使用AdoDB操作MySQL,下面给出AdoDB相应的代码方便大家查阅:

-------------------------------------------------------------------------------

<?php 
// ... 

$adodb->startTrans
();

//实际,getOne所调用的查询也可以直接放到rowLock来进行,这里只是为了演示效果能更明显些。

$adodb->rowLock('book''book_id = 123'
); 

$bookNumber $adodb->getOne("SELECT book_number FROM book WHERE  book_id = 123"
); 

$adodb->execute("UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123"
); 

$adodb->completeTrans
(); 

// ... 
?>

-------------------------------------------------------------------------------

其中,rowLock的方法就是调用的FOR UPDATE来实现的行锁,你可能会想把“FOR UPDATE”直接写到$adodb->getOne()调用的那条SQL语句里面去实现行锁的功能,不错,那样确实可以,但是并不是所有的数据库都使用“FOR UPDATE”语法来实现行锁功能,比如Sybase使用“HOLDLOCK”的语法来实现行锁功能,所以为了你的数据库抽象层保持可移植性,我还是劝你用rowLock来实现行锁功能,至于可移植性就交给AdoDB好了,嗯,有点扯远了,今儿就说到这里了。

-------------------------------------------------------------------------------

附:

AdoDB中存在一个setTransactionMode()方法,能够设置事务的隔离级别,如下:

SetTransactionMode allows you to pass in the transaction mode to use for all subsequent transactions for that connection session. Note: if you have persistent connections and using mysql or mssql, you might have to explicitly reset your transaction mode at the beginning of each page request. This is only supported in postgresql, mssql, mysql with InnoDB and oci8 currently. For example:

$db->SetTransactionMode("SERIALIZABLE");
$db->BeginTrans();
$db->Execute(...); $db->Execute(...);
$db->CommiTrans();

$db->SetTransactionMode(""); // restore to default
$db->StartTrans();
$db->Execute(...); $db->Execute(...);
$db->CompleteTrans();

Supported values to pass in:

    * READ UNCOMMITTED (allows dirty reads, but fastest)
    * READ COMMITTED (default postgres, mssql and oci8)
    * REPEATABLE READ (default mysql)
    * SERIALIZABLE (slowest and most restrictive)

You can also pass in database specific values such as 'SNAPSHOT' for mssql or 'READ ONLY' for oci8/postgres.

1
0
分享到:
评论

相关推荐

    mysql怎么减少行锁对性能的影响

    在MySQL数据库中,行锁是一种重要的锁定机制,它主要应用于InnoDB存储引擎中,用于控制并发事务对数据行的访问。行锁的使用可以显著提高系统的并发处理能力,但同时也会带来一定的性能开销。因此,了解如何有效地...

    PHP+redis+mysql innodb事务和行锁实现秒杀系统.zip

    它支持面向过程、面向对象以及函数式编程范式,可根据项目需求和开发者喜好灵活选择。PHP内置丰富的数据类型(如整型、浮点型、字符串、数组、对象等),并提供了大量的预定义函数,涵盖了字符串操作、数学运算、...

    spring-transaction-manager:Spring事务管理实现原理及MySQL InnoBD引擎行锁概述---配套代码

    总的来说,理解Spring事务管理的原理和MySQL InnoDB引擎的行锁机制对于开发高效、稳定的应用至关重要。这包括熟悉如何配置和使用不同的事务管理器,掌握声明式事务管理的使用,以及在数据库层面了解事务隔离级别和锁...

    06-VIP-深入理解Mysql事务隔离级别与锁机制.pdf

    深入理解Mysql事务隔离级别与锁机制 Mysql事务隔离级别与锁机制是数据库系统中非常重要的概念,它们都是为了解决多事务并发问题而设计的。下面我们将深入讲解这些机制,让大家彻底理解数据库内部的执行原理。 事务...

    MySQL事务表和非事务表的区别1

    综上所述,MySQL的事务表和非事务表各有优劣,选择哪种类型取决于应用的需求,包括对数据一致性的要求、并发性能、存储空间和速度等因素。在设计数据库时,理解这些区别至关重要,以便做出最适合业务需求的决策。

    【课堂笔记】MySQL事务与锁详解.pdf

    MySQL事务与锁详解的知识点如下: 1. 事务的理解与应用场景: 事务是数据库管理系统(DBMS)执行过程中的逻辑单位,是一个不可分割的工作单元,可以包含一条或多条DML语句。在实际应用中,事务常见的使用场景包括:当...

    61 表锁和行锁互相之间的关系以及互斥规则是什么呢?l.pdf

    MySQL中表锁和行锁的互斥规则是一门较为复杂但十分重要的知识,尤其在数据库并发控制的场景下,理解这些规则能够帮助开发者和数据库管理员更好地设计和管理数据库应用。本篇文章将详细地阐述表锁和行锁在MySQL中的...

    MySQL数据库原理及设计方法.pdf

    总的来说,MySQL数据库原理及设计方法涉及到网络通信、查询优化、并发控制、事务处理等多个层面,理解这些知识对于有效地使用和管理MySQL数据库至关重要。通过合理的设计和配置,可以确保MySQL在高并发环境下提供...

    深入理解Mysql事务隔离级别与锁机制.pdf

    "深入理解Mysql事务隔离级别与锁机制" 事务隔离级别是数据库系统中的一种机制,用于解决多事务并发问题,使得事务之间的执行不受影响。这种机制可以分为四个级别:Read Uncommitted、Read Committed、Repeatable ...

    详解Mysql事务隔离级别与锁机制.doc

    本篇文章详细介绍了Mysql事务隔离级别与锁机制的概念、原理和应用。事务隔离级别是数据库系统中的一种机制,用于解决多事务并发问题,包括脏写、不可重复读、幻读等问题。锁机制是数据库系统中的一种机制,用于解决...

    mysql中的事务、锁讲解和操作

    MySQL中的事务和锁是数据库管理中的关键概念,对于保证数据的一致性和完整性至关重要。...无论是初学者还是经验丰富的开发者,都需要深入研究这些概念,以便在实际项目中更好地应用MySQL的事务和锁功能。

    47 简单回顾一下, MySQL运行时多个事务同时执行是什么场景?l.pdf

    redo log和undo log是MySQL事务中用于保证数据一致性的关键机制。redo log记录了事务的所有修改操作,即使在事务提交之前系统崩溃,也能通过redo log中的记录来恢复已经进行的修改。如果事务正常提交,redo log会被...

    (mysql面试题)MySQL中的事务和锁的概念及其作用及代码展示.txt

    合理使用事务和锁可以帮助开发者更好地管理和控制数据库中的数据,从而提高应用程序的稳定性和性能。在实际开发中,还需要根据具体的应用场景选择合适的事务隔离级别和锁类型,以达到最佳的并发控制效果。

    MySQL:锁机制.pdf

    在MySQL中,锁机制是保障数据操作安全的重要组成部分,其主要分为表锁、行锁和间隙锁。本知识点详细解读了MySQL锁机制的定义、分类、特点以及具体的应用案例。 1. 锁的定义和分类 在数据库系统中,锁是用来控制多...

    MySQL数据库体系架构介绍.pptx

    InnoDB存储引擎是MySQL数据库的默认存储引擎,支持事务、行锁、支持MVCC多版本并发控制、并发性高,适合OLTP应用场景。MyISAM存储引擎不支持事务、表锁、并发很低、资源利用低,适合OLAP应用场景。Memory存储引擎将...

    深入理解Mysql锁与事务隔离级别1

    本文将详细阐述MySQL中的锁机制以及事务的四种隔离级别,以帮助读者更好地理解和应用这些关键概念。 一、锁的概念与分类 1.1 锁的定义 锁是一种用于控制多进程或线程对共享资源访问的同步机制。在数据库中,它...

    MySQL数据库相关课件PPT

    MySQL数据库是全球最受欢迎的开源关系型数据库管理系统之一,它以其高效、可靠和易于维护的特点,在Web开发、企业级应用和数据分析等领域广泛应用。本课件PPT涵盖了MySQL的基础知识到高级特性的深入理解,旨在帮助...

    深入理解MySQL核心技术_MYSQL_

    InnoDB是默认引擎,支持事务处理和行级锁定,适合大型并发应用;MyISAM则以读取速度快、空间占用少为特点,适用于读多写少的场景。 2. **SQL语句解析**:MySQL通过解析器将SQL语句转化为可执行的查询计划。了解SQL...

    mysql-4.pdf

    综上所述,理解MySQL中的行锁机制、InnoDB内存结构、RedoLog与UndoLog的作用,以及事务的ACID特性,对于优化数据库性能和保证数据一致性具有重要意义。通过合理的事务管理和索引设计,可以有效地提升MySQL数据库在高...

Global site tag (gtag.js) - Google Analytics