个人博客里面还有其他Mysql教程
,需要的同学可以点击下面链接
悲观锁
悲观锁的原理是什么呢,就是给c事务里面的某一个sql语句添加一把锁,当a线程执行这个有锁的sql语句的时候,b线程就只能执行到c事务里面这个有锁的sql语句前面的一些sql语句,然后等到a线程执行完这个有锁的sql语句的事务的时候,b线程才能执行c事务里面有锁的sql语句
悲观锁解决购买商品的并发问题
什么是购买商品的并发问题呢?
就是有一个商品的数量是1,然后有两个用户a和b都来购买这个商品,两个用户也就是两个线程,然后呢用户购买一个商品有下面的几步
1)查询该商品的数量,
2)进行库存判断,
3)往订单表里面进行插入这个数据,
4)然后把该商品的数量减1,
假设两个线程都执行到了第2步结束,然后呢,这样就会造成一个问题,就是此时的商品数量是1,然后两个用户都执行到了第2步结束,a用户先完成第3和第4步,此时的商品的数量就是0了,然后b用户此时就有问题了,因为此时的商品已经是0了,那么b用户还买个毛的商品,这就是并发问题,就像下面的图片
代码展示购买商品时候的并发问题
首先数据库里面的数据如下,此时只有鸡腿这个商品的数量是1
所有文件的路径如下
Web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>myweb</display-name>
<servlet>
<description></description>
<display-name>ProductServlet</display-name>
<servlet-name>ProductServlet</servlet-name>
<servlet-class>com.itcast.servlet.ProductServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ProductServlet</servlet-name>
<url-pattern>/product</url-pattern>
</servlet-mapping>
</web-app>
Product
package com.itcast.domain;
public class Product {
private Integer pid;
private Double price;
private String pname;
private Integer pnum;
public Integer getPid() {
return pid;
}
public void setPid(Integer pid) {
this.pid = pid;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public String getPname() {
return pname;
}
public void setPname(String pname) {
this.pname = pname;
}
public Integer getPnum() {
return pnum;
}
public void setPnum(Integer pnum) {
this.pnum = pnum;
}
@Override
public String toString() {
return "Product [pid=" + pid + ", price=" + price + ", pname=" + pname + ", pnum=" + pnum + "]";
}
}
ProductServlet代码如下
此时我们访问两个url路径http://localhost:8080/myweb/product?uname=jack 和http://localhost:8080/myweb/product?uname=tom,首先tom这个线程先查询数据库里面的鸡腿的商品数量是1,然后呢因为下面的Thread.sleep(5000),tom这个线程就睡眠了,然后jack这个线程在查询数据库里面的鸡腿的数量还是1,所以先打印 ,然后因为下面的Thread.sleep(5000),然后jack这个线程也睡眠了,然后tom这个线程就先购买成功了,所以会打印 ,此时的数据库里面的鸡腿的数量就会从1变成0,然后呢jack这个线程在购买成功,所以会打印 ,但是此时数据库里面的鸡腿的数据就会从0变成-1,这是不合理的,所以jack这个线程算没有购买成功的
package com.itcast.servlet;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import com.itcast.domain.Product;
public class ProductServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Connection con=null;
String user = request.getParameter("uname");
try {
Class.forName("com.mysql.jdbc.Driver");
con= (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
QueryRunner qr = new QueryRunner();
//开启事务
con.setAutoCommit(false);
String select_product = "SELECT * FROM product";
Product product =qr.query(con, select_product, new BeanHandler<Product>(Product.class));
System.out.println(user+"查询商品数量是"+product.getPnum());
Thread.sleep(5000);
if(product.getPnum()>0){
String delete_product = "update product set pnum=pnum-1 where 1";
int row = qr.update(con,delete_product);
if(row>0){
System.out.println(user+"购买成功了");
}else{
System.out.println(user+"没有购买成功");
}
}else{
System.out.println(user+"没有购买成功");
}
con.commit();
} catch (Exception e) {
try {
System.out.println(user+"没有购买成功");
con.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
然后我们看到数据库里面的鸡腿的商品数量变成-1了,这也就说明后一个jack这个用户并没有购买成功,因为jack购买的是0到-1的那个商品,那个商品是不存在的
悲观锁解决购买商品的并发问题
比如下面的代码
首先数据库里面的数据如下,此时只有鸡腿这个商品的数量是1
所有文件的路径如下
Web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>myweb</display-name>
<servlet>
<description></description>
<display-name>ProductServlet</display-name>
<servlet-name>ProductServlet</servlet-name>
<servlet-class>com.itcast.servlet.ProductServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ProductServlet</servlet-name>
<url-pattern>/product</url-pattern>
</servlet-mapping>
</web-app>
Product
package com.itcast.domain;
public class Product {
private Integer pid;
private Double price;
private String pname;
private Integer pnum;
public Integer getPid() {
return pid;
}
public void setPid(Integer pid) {
this.pid = pid;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public String getPname() {
return pname;
}
public void setPname(String pname) {
this.pname = pname;
}
public Integer getPnum() {
return pnum;
}
public void setPnum(Integer pnum) {
this.pnum = pnum;
}
@Override
public String toString() {
return "Product [pid=" + pid + ", price=" + price + ", pname=" + pname + ", pnum=" + pnum + "]";
}
}
ProductServlet代码如下,此时当访问两个url路径http://localhost:8080/myweb/product?uname=jack 和http://localhost:8080/myweb/product?uname=tom的时候,只有当tom这个线程在查询商品,然后购买商品结束之后,jack这个线程才去查询商品的数量,这个就是悲观锁的作用,这也就避免了上面的并发问题
package com.itcast.servlet;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import com.itcast.domain.Product;
public class ProductServlet extends HttpServlet {
public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Connection con=null;
String user = request.getParameter("uname");
try {
Class.forName("com.mysql.jdbc.Driver");
con= (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
QueryRunner qr = new QueryRunner();
//开启事务
con.setAutoCommit(false);
String select_product = "SELECT * FROM product for update";
Product product =qr.query(con, select_product, new BeanHandler<Product>(Product.class));
System.out.println(user+"查询商品数量是"+product.getPnum());
Thread.sleep(5000);
if(product.getPnum()>0){
String delete_product = "update product set pnum=pnum-1 where 1";
int row = qr.update(con,delete_product);
if(row>0){
System.out.println(user+"购买成功了");
}else{
System.out.println(user+"没有购买成功");
}
}else{
System.out.println(user+"没有购买成功");
}
con.commit();
} catch (Exception e) {
try {
System.out.println(user+"没有购买成功");
con.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
结束之后,鸡腿商品的数量就变成了0
能看到这里的同学,就帮忙点个赞吧,Thanks♪(・ω・)ノ