MySQL 트랜잭션 및 데이터 일관성 처리에 대한 자세한 설명
- 小云云원래의
- 2017-12-18 15:39:251652검색
이 글에서는 Mysql 트랜잭션과 데이터 일관성 처리에 대해 자세히 설명하겠습니다. 직장에서 이런 문제가 자주 발생하는데, 사용 가능한 인벤토리를 쿼리하고 수정하려고 할 때 다른 사용자가 인벤토리 데이터를 수정했을 수도 있습니다. 아래의 솔루션을 살펴보겠습니다.
MySQL의 InnoDB에서 기본 Tansaction 격리 수준은 REPEATABLE READ입니다.
SELECT 이후 동일한 양식을 UPDATE하려면 SELECT... UPDATE를 사용하는 것이 가장 좋습니다.
예:
상품의 수량을 저장하는 상품 형태에 수량이 있다고 가정해 보겠습니다. 주문이 성립되기 전에 상품의 수량이 충분한지(수량>0) 판단해야 합니다. 수량은 1로 업데이트됩니다. 코드는 다음과 같습니다.
SELECT quantity FROM products WHERE id=3; UPDATE products SET quantity = 1 WHERE id=3;
왜 안전하지 않습니까?
작은 경우에는 문제가 없을 수도 있지만, 많은 양의 데이터 액세스는 "확실히" 문제를 일으킬 것입니다. 수량>0일 때 재고를 공제해야 하는 경우 첫 번째 SELECT 라인에서 프로그램이 읽은 수량을 2라고 가정합니다. 숫자는 올바른 것 같지만 MySQL이 UPDATE를 준비할 때 누군가 이미 재고를 공제했을 수 있습니다. . 0이 되었는데, 프로그램이 이를 모르고 잘못된 UPDATE를 계속하였다. 따라서 읽고 제출한 데이터가 올바른지 확인하기 위해 트랜잭션 메커니즘을 사용해야 합니다.
그래서 MySQL에서 이렇게 테스트할 수 있습니다. 코드는 다음과 같습니다.
SET AUTOCOMMIT=0; BEGIN WORK; SELECT quantity FROM products WHERE id=3 FOR UPDATE;
이때 제품 데이터에서 id=3인 데이터는 잠겨 있으며(참고 3), 다른 트랜잭션은 이 트랜잭션을 기다려야 합니다. 실행되기 전에 제출해야 합니다
SELECT * FROM products WHERE id=3 FOR UPDATE
이것입니다. 다른 트랜잭션에서 수량으로 읽은 숫자가 올바른지 확인할 수 있습니다.
UPDATE products SET quantity = '1' WHERE id=3 ; COMMIT WORK;
커밋이 데이터베이스에 기록되고 제품이 잠금 해제됩니다.
참고 1: BEGIN/COMMIT는 트랜잭션의 시작 및 끝 지점입니다. 두 개 이상의 MySQL 명령 창을 사용하여 잠금 상태를 대화식으로 관찰할 수 있습니다.
참고 2: 트랜잭션 중에는 동일한 데이터가 있는 SELECT ... FOR UPDATE 또는 LOCK IN SHARE MODE만 실행되기 전에 다른 트랜잭션이 끝날 때까지 기다립니다. 일반적으로 SELECT...는 이에 영향을 받지 않습니다.
참고 3: InnoDB는 기본적으로 행 수준 잠금을 사용하므로 데이터 열 잠금에 대해서는 이 문서를 참조하세요.
참고 4: InnoDB 형식에서 LOCK TABLES 명령어를 사용하지 마세요. 최후의 수단으로 사용해야 하는 경우 시스템에서 빈번한 교착 상태를 방지하려면 InnoDB에서 LOCK TABLES 사용에 대한 공식 지침을 읽어보세요.
더 고급 사용법
먼저 쿼리한 후 데이터를 업데이트해야 하는 경우 다음과 같은 문을 사용하는 것이 가장 좋습니다.
UPDATE products SET quantity = '1' WHERE id=3 AND quantity > 0;
이렇게 하면 항목을 추가하지 않고도 처리할 수 있습니다.
MySQL은 높은 동시성을 처리하고 재고 과매출을 방지합니다
정말 좋은 글을 보고 배워보려고 옮겨봤습니다.
Wang 선생님은 오늘 우리에게 또 다른 교훈을 주셨습니다. 사실 Wang 선생님은 작년에 MySQL이 높은 동시성을 처리하고 재고 과잉을 방지하는 문제를 언급했지만, 당시에는 모두가 그것을 이해했지만 실제로는 안타깝습니다. 개발이 진행 중이지만 아직 이 부분에 대한 인식이 없습니다. 오늘은 이 문제에 대해 제가 이해한 부분을 정리하고, 앞으로도 이런 강좌가 많아졌으면 좋겠습니다.
먼저, 과잉 판매된 재고 문제를 설명하겠습니다. 일반적으로 전자 상거래 웹사이트에서는 공동 구매, 플래시 세일, 특별 제안과 같은 활동이 발생합니다. 이러한 활동의 일반적인 특징은 수천 또는 심지어 수십 건의 방문 급증입니다. 수천명의 사람들이 제품을 구매하기 위해 달려갑니다. 그러나 활성 상품으로서 재고는 확실히 매우 제한되어 있습니다. 과매수와 불필요한 손실을 방지하기 위해 재고를 관리하는 방법은 많은 전자상거래 웹사이트 프로그래머에게 가장 기본적인 문제이기도 합니다.
기술적인 관점에서 많은 분들이 당연히 거래를 떠올리시겠지만, 거래는 과매도 재고 관리를 위한 필요조건이지, 필요충분조건은 아닙니다.
예:
총 재고: 4개 상품
요청자: a, 1개 상품, 2개 상품 c, 3개 상품
절차는 다음과 같습니다.
beginTranse(开启事务)
try{
$result = $dbca->query('select amount from s_store where postID = 12345');
if(result->amount > 0){
//quantity为请求减掉的库存数量
$dbca->query('update s_store set amount = amount - quantity where postID = 12345');
}
}catch($e Exception){
rollBack(回滚)
}
commit(提交事务)
위 코드는 우리가 일반적으로 재고 관리를 위해 작성하는 코드입니다. 네, 대부분의 사람들이 이렇게 쓸 겁니다. 별 문제 아닌 것 같지만 사실은 엄청난 허점이 숨겨져 있습니다. 데이터베이스 액세스는 실제로 디스크 파일에 대한 액세스입니다. 데이터베이스의 테이블은 실제로 디스크에 저장된 파일이며 하나의 파일에도 여러 테이블이 포함됩니다. 예를 들어 높은 동시성으로 인해 현재 a, b, c 세 명의 사용자가 이 거래에 참여했습니다. 이때 공유 잠금이 생성되므로 선택 시 이 세 명의 사용자가 찾은 재고 수량이 모두 4개입니다. 또한 mysql innodb에서 찾은 결과는 버전 관리하에 있습니다. 다른 사용자가 업데이트하고 커밋하기 전(즉, 새 버전이 생성되기 전) 현재 사용자가 찾은 결과는 여전히 동일한 버전입니다. , 이 세 명의 사용자가 동시에 업데이트에 도착하면 업데이트 문은 이때 동시성을 직렬화합니다. 즉, 동시에 도착한 세 명의 사용자를 정렬하고 하나씩 실행하며 배타적 잠금이 설정됩니다. 업데이트 문이 커밋되기 전에 다른 사용자는 실행을 기다리고 있습니다. 커밋 후에는 새 버전이 생성되며 인벤토리는 확실히 음수가 됩니다. 하지만 위의 설명에 따르면 코드를 수정하면 과매수 현상이 발생하지 않습니다. 코드는 다음과 같습니다.
beginTranse(开启事务)
try{
//quantity为请求减掉的库存数量
$dbca->query('update s_store set amount = amount - quantity where postID = 12345');
$result = $dbca->query('select amount from s_store where postID = 12345');
if(result->amount < 0){
throw new Exception('库存不足');
}
}catch($e Exception){
rollBack(回滚)
}
commit(提交事务)그리고 더 간결한 방법은
beginTranse(开启事务)
try{
//quantity为请求减掉的库存数量
$dbca->query('update s_store set amount = amount - quantity where amount>=quantity and postID = 12345');
}catch($e Exception){
rollBack(回滚)
}
commit(提交事务)
=====================================================================================
1、在秒杀的情况下，肯定不能如此高频率的去读写数据库，会严重造成性能问题的
必须使用缓存，将需要秒杀的商品放入缓存中，并使用锁来处理其并发情况。当接到用户秒杀提交订单的情况下，先将商品数量递减（加锁/解锁）后再进行其他方面的处理，处理失败在将数据递增1（加锁/解锁），否则表示交易成功。
当商品数量递减到0时，表示商品秒杀完毕，拒绝其他用户的请求。
2、这个肯定不能直接操作数据库的，会挂的。直接读库写库对数据库压力太大，要用缓存。
把你要卖出的商品比如10个商品放到缓存中；然后在memcache里设置一个计数器来记录请求数，这个请求书你可以以你要秒杀卖出的商品数为基数，比如你想卖出10个商品，只允许100个请求进来。那当计数器达到100的时候，后面进来的就显示秒杀结束，这样可以减轻你的服务器的压力。然后根据这100个请求，先付款的先得后付款的提示商品以秒杀完。
3、首先，多用户并发修改同一条记录时，肯定是后提交的用户将覆盖掉前者提交的结果了。
这个直接可以使用加锁机制去解决，乐观锁或者悲观锁。乐观锁:，就是在数据库设计一个版本号的字段，每次修改都使其+1，这样在提交时比对提交前的版本号就知道是不是并发提交了，但是有个缺点就是只能是应用中控制，如果有跨应用修改同一条数据乐观锁就没办法了，这个时候可以考虑悲观锁。
悲观锁:，就是直接在数据库层面将数据锁死，类似于oralce中使用select xxxxx from xxxx where xx=xx for update，这样其他线程将无法提交数据。
除了加锁的方式也可以使用接收锁定的方式，思路是在数据库中设计一个状态标识位，用户在对数据进行修改前，将状态标识位标识为正在编辑的状态，这样其他用户要编辑此条记录时系统将发现有其他用户正在编辑，则拒绝其编辑的请求，类似于你在操作系统中某文件正在执行，然后你要修改该文件时，系统会提醒你该文件不可编辑或删除。
4、不建议在数据库层面加锁，建议通过服务端的内存锁（锁主键）。当某个用户要修改某个id的数据时，把要修改的id存入memcache，若其他用户触发修改此id的数据时，读到memcache有这个id的值时，就阻止那个用户修改。
5、实际应用中，并不是让mysql去直面大并发读写，会借助“外力”，比如缓存、利用主从库实现读写分离、分表、使用队列写入等方法来降低并发读写。
悲观锁和乐观锁
首先，多用户并发修改同一条记录时，肯定是后提交的用户将覆盖掉前者提交的结果了。这个直接可以使用加锁机制去解决，乐观锁或者悲观锁。
悲观锁(Pessimistic Lock), 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
乐观锁(Optimistic Lock), 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。
两种锁各有优缺点，不能单纯的定义哪个好于哪个。乐观锁比较适合数据修改比较少，读取比较频繁的场景，即使出现了少量的冲突，这样也省去了大量的锁的开销，故而提高了系统的吞吐量。但是如果经常发生冲突（写数据比较多的情况下），上层应用不不断的retry，这样反而降低了性能，对于这种情况使用悲观锁就更合适。
实战
对这个表的 amount 进行修改，开两个命令行窗口
第一个窗口A;
SET AUTOCOMMIT=0; BEGIN WORK; SELECT * FROM order_tbl WHERE order_id='124' FOR UPDATE;
第二个窗口B：
# 更新订单ID 124 的库存数量 UPDATE `order_tbl` SET amount = 1 WHERE order_id = 124;
我们可以看到窗口A加了事物，锁住了这条数据，窗口B执行时会出现这样的问题：
第一个窗口完整的提交事物：
SET AUTOCOMMIT=0; BEGIN WORK; SELECT * FROM order_tbl WHERE order_id='124' FOR UPDATE; UPDATE `order_tbl` SET amount = 10 WHERE order_id = 124; COMMIT WORK;
相关推荐：
위 내용은 MySQL 트랜잭션 및 데이터 일관성 처리에 대한 자세한 설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!