MySQL的悲观锁（共享锁、排他锁）

悲观锁之共享锁、作用于【两表业务一致时使用】、锁住A表数据不被修改（但可以用来查询）、从而来修改B表数据

在使用lock in share mode下当B阻塞时，如果A继续有修改数据，那么此时B会终止失败

lock in share mode对记录加读锁，此时记录能被其他线程加读锁，不能加写锁

select * from classes where id=80 lock in share mode;	-- 锁住当前这条数据、不允许对该数据做删除和修改操作、否则会抛出异常、可以不加锁查询、可以加共享锁查询、但不可加排他锁查询
update student set classid=80 where id=1001

悲观锁之排他锁、作用于【单表的业务】、单表锁住后，直至我读写完数据后、别人才能读写数据

并发时for update会使B一直阻塞，等待A操作完成后执行B操作；

并发时for update 对记录加写锁，此时记录不能被其他线程加读锁或者写锁。

必须按照锁的先后顺序执行

select * from student FOR UPDATE;		-- 锁住这张表的查询和修改操作、其他线程可以查询、但不可修改、亦不可加锁查询
delete from student where id=23763;

案例代码：

public interface StudentDao {

	@Select("select * from student where id=#{id} lock in share mode")
	List<Student> getStudentAll(int id);
	
	@Delete("delete from student where id=#{id}")
	int delete(int id);
	
	@Update("update student set name=#{name},pwd=#{pwd} where id=#{id}")
	int update(@Param("name")String name,@Param("pwd")String pwd,@Param("id")int id);
	
	
	@Select("select * from student where id=#{id} for update")
	List<Student> getStudentAll22(int id);
}

public class Demo {
	public static void main(String[] args)throws Exception {
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		//是session工厂、专门用来创建SqlSession的
		//SqlSession就是Connection对象
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		
		final SqlSession session1 = sqlSessionFactory.openSession(false);
		final SqlSession session2 = sqlSessionFactory.openSession(false);
		
		final StudentDao dao1 = session1.getMapper(StudentDao.class);
		final StudentDao dao2 = session2.getMapper(StudentDao.class);
		
		final int id=29;
		
		new Thread(new Runnable() {
			public void run() {
				
				dao1.getStudentAll(id);
				System.out.println("dao1、查询数据完毕");
				
				try {
					Thread.sleep(10000);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
				
				int i = dao1.update("aa", "aa", id);
				System.out.println("dao1、修改数据完毕"+i);
				session1.commit();
				System.out.println("dao1操作结束");
			}
		}).start();
		
		new Thread(new Runnable() {
			public void run() {
				try {
					Thread.sleep(1000);
				} catch (InterruptedException e1) {
					e1.printStackTrace();
				}
				dao2.getStudentAll22(id);
				System.out.println("dao2读取数据完毕");
				try {
					Thread.sleep(1000);
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
				
				int i = dao2.delete(id);
				System.out.println("dao2删除数据："+i);
				
				session2.commit();
				System.out.println("dao2操作结束");
			}
		}).start();
	
		
		System.out.println("over");
	}
}