本文目录一览：
MySQL数据库表锁定的几种方法实现
如果两个程序都向表中写数据显然会造成很大的麻烦，甚至会有意外情况发生。如果表正由一个程序写入，同时进行读取的另一个程序也会产生混乱的结果。
锁定表的方法
防止客户机的请求互相干扰或者服务器与维护程序相互干扰的方法主要有多种。如果你关闭数据库，就可以保证服务器
和myisamchk和isamchk之间没有交互作用。但是停止服务器的运行并不是一个好注意，因为这样做会使得没有故障的数据库和表也不可用。本节主
要讨论的过程，是避免服务器和myisamchk或isamchk之间的交互作用。实现这种功能的方法是对表进行锁定。
服务器由两种表的锁定方法：
1.内部锁定
内部锁定可以避免客户机的请求相互干扰——例如，避免客户机的SELECT查询被另一个客户机的UPDATE查询所干扰。也可以利用内部锁定机制防止服务器在利用myisamchk或isamchk检查或修复表时对表的访问。
语法：锁定表：LOCK TABLES
tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]
解锁表：UNLOCKTABLESLOCKTABLES为当前线程锁定表。UNLOCK TABLES释放被当前线程持有的任何锁。当线程发出另外一个LOCK
TABLES时，或当服务器的连接被关闭时，当前线程锁定的所有表自动被解锁。
如果一个线程获得在一个表上的一个READ锁，该线程(和所有其他线程)只能从表中读。如果一个线程获得一个表上的一个WRITE锁，那么只有持锁的线程READ或WRITE表，其他线程被阻止。
每个线程等待(没有超时)直到它获得它请求的所有锁。
WRITE锁通常比READ锁有更高的优先级，以确保更改尽快被处理。这意味着，如果一个线程获得READ锁，并且然后另外一个线程请求一个WRITE锁,
随后的READ锁请求将等待直到WRITE线程得到了锁并且释放了它。
显然对于检查，你只需要获得读锁。再者钟情跨下，只能读取表，但不能修改它，因此他也允许其它客户机读取表。对于修复，你必须获得些所以防止任何客户机在你对表进行操作时修改它。
2.外部锁定
服务器还可以使用外部锁定(文件级锁)来防止其它程序在服务器使用表时修改文件。通常，在表的检查操作中服务器
将外部锁定与myisamchk或isamchk作合使用。但是，外部锁定在某些系统中是禁用的，因为他不能可靠的进行工作。对运行myisamchk或
isamchk所选择的过程取决于服务器是否能使用外部锁定。如果不使用，则必修使用内部锁定协议。
如果服务器用--skip-locking选项运行，则外部锁定禁用。该选项在某些系统中是缺省的，如Linux。可以通过运行mysqladmin
variables命令确定服务器是否能够使用外部锁定。检查skip_locking变量的值并按以下方法进行：
◆
如果skip_locking为off，则外部锁定有效您可以继续并运行人和一个实用程序来检查表。服务器和实用程序将合作对表进行访问。但是，运行任何
一个实用程序之前，应该使用mysqladmin flush-tables。为了修复表，应该使用表的修复锁定协议。
◆
如果skip_locaking为on，则禁用外部锁定，所以在myisamchk或isamchk检查修复表示服务器并不知道，最好关闭服务器。如果坚
持是服务器保持开启状态，月确保在您使用此表示没有客户机来访问它。
mysql存储过程出现锁表锁行的情况怎么解决
行锁的等待
在介绍如何解决行锁等待问题前，先简单介绍下这类问题产生的原因。产生原因简述：当多个事务同时去操作（增删改）某一行数据的时候，MySQL 为了维护 ACID 特性，就会用锁的形式来防止多个事务同时操作某一行数据，避免数据不一致。只有分配到行锁的事务才有权力操作该数据行，直到该事务结束，才释放行锁，而其他没有分配到行锁的事务就会产生行锁等待。如果等待时间超过了配置值（也就是 innodb_lock_wait_timeout 参数的值，个人习惯配置成 5s，MySQL 官方默认为 50s），则会抛出行锁等待超时错误。
如上图所示，事务 A 与事务 B 同时会去 Insert 一条主键值为 1 的数据，由于事务 A 首先获取了主键值为 1 的行锁，导致事务 B 因无法获取行锁而产生等待，等到事务 A 提交后，事务 B 才获取该行锁，完成提交。这里强调的是行锁的概念，虽然事务 B 重复插入了主键，但是在获取行锁之前，事务一直是处于行锁等待的状态，只有获取行锁后，才会报主键冲突的错误。当然这种 Insert 行锁冲突的问题比较少见，只有在大量并发插入场景下才会出现，项目上真正常见的是 updatedelete 之间行锁等待，这里只是用于示例，原理都是相同的。
三、产生的原因根据我之前接触到的此类问题，大致可以分为以下几种原因
java程序中如何实现对mysql数据库中表的锁定
方法1：用mysql命令锁住表.
public void test() {
String sql = "lock tables aa1 write";
// 或String sql = "lock tables aa1 read";
// 如果想锁多个表 lock tables aa1 read ,aa2 write , .....
String sql1 = "select * from aa1 ";
String sql2 = "unlock tables";
try {
this.pstmt = conn.prepareStatement(sql);
this.pstmt1 = conn.prepareStatement(sql1);
this.pstmt2 = conn.prepareStatement(sql2);
pstmt.executeQuery();
pstmt1.executeQuery();
pstmt2.executeQuery();
} catch (Exception e) {
System.out.println("异常" + e.getMessage());
}
}
对于read lock 和 write lock官方说明：
1.如果一个线程获得一个表的READ锁定，该线程（和所有其它线程）只能从该表中读取。
如果一个线程获得一个表的WRITE锁定，只有保持锁定的线程可以对表进行写入。
其它的线程被阻止，直到锁定被释放时为止。
2.当您使用LOCK TABLES时，您必须锁定您打算在查询中使用的所有的表。
虽然使用LOCKTABLES语句获得的锁定仍然有效，但是您不能访问没有被此语句锁定的任何的表。
同时，您不能在一次查询中多次使用一个已锁定的表——使用别名代替，
在此情况下，您必须分别获得对每个别名的锁定。
对与read lock 和 write lock个人说明：
1.read lock 和 write lock 是线程级(表级别).
2.在同一个会话中加了read lock锁. 只能对这个表进行读操作.对这个表以外的任何表都无法进行增、删、改、查的操作.
但是在不同会话中，只能对加了read lock的表进行读操作.但可以对read lock以外的表进行增、删、改、查的操作.
3.在同一个会话中加了write lock锁.只能对这个表进行读、写操作.对这个表以外的任何表都无法进行增、删、改、查的操作.
但是在不同会话中,无法对加了write lock的表进行读、写操作.但可以对write lock以外的表进行增、删、改、查的操作.
4.如果表中使用了别名.(SELECT * FROM aa1 AS byname_table)
在对aa1加锁时，必须把别名加上去(lock tables aa1 as byname_table read)
在同一个会话中.必须使用别名进行查询.
在不同的会话中.可以不需要使用别名进行查询.
5.在多个会话中可以对同一个表进行lock read操作.但不能在多个会话中对同一个表进行lock write操作（这些锁将等待已锁的表释放自身的线程锁）
如果多个会话对同一个表进行lock read操作.那么在这些会话中,也只能对以锁的表进行读操作.
6.如果要你锁住了一个表，需要嵌套查询.你必须使用别名，并且，要锁定别名.
例如.lock table aa1 read ,aa1 as byname_table read;
select * from aa1 where id in (select * from aa1 as xx where id=2);
7.解锁必须用unlock tables;
另:
在JAVA程序中,要想解锁,需要调用 unlock tables来解锁.
如果没有调用unlock tables.
关闭connection 、程序结束 、调用GC 都能解锁.
方法2：用记录锁锁表.
public void test() {
String sql = "select * from aa1 for update";
// select * from aa1 lock in share mode;
try {
conn.setAutoCommit(false);
this.pstmt = conn.prepareStatement(sql);
pstmt.executeQuery();
} catch (Exception e) {
System.out.println("异常" + e.getMessage());
}
}
1.for update 与 lock in share mode 属于行级锁和页级锁
2.for update 排它锁,lock in share mode 共享锁
3.对于记录锁.必须开启事务.
4.行级锁定事实上是索引记录的锁定.只要是用索引扫描的行（或没索引全表扫描的行）,都将被锁住.
5.在不同的隔离级别下还会使用next-key locking算法.即所扫描的行之间的“间隙”也会也锁住(在Repeatable read和Serializable隔离级别下有间隙锁).
6.在mysql中共享锁的含义是:在被共享锁锁住的行,即使内容被修改且并没有提交.在另一个会话中依然看到最新修改的信息.
在同一会话中加上了共享锁.可以对这个表以及这个表以外的所有表进行增、删、改、查的操作.
在不同的会话中.可以查到共享锁锁住行的最新消息.但是在Read Uncommitted隔离级别下不能对锁住的表进行删，
改操作.（需要等待锁释放才能操作...）
在Read Committed隔离级别下不能对锁住的表进行删，改操作.（需要等待锁释放才能操作...）
在Repeatable read隔离级别下不能对锁住行进行增、删、改操作.（需要等待锁释放才能操作...）
在Serializable隔离级别下不能对锁住行进行增、删、改操作. （需要等待锁释放才能操作...）
7.在mysql中排他锁的含义是:在被排它锁锁住的行,内容修改并没提交，在另一个会话中不会看到最新修改的信息。
在不同的会话中.可以查到共享锁锁住行的最新消息.但是Read Uncommitted隔离级别下不能对锁住的表进行删，
改操作.（需要等待锁释放才能操作...）
在Read Committed隔离级别下不能对锁住的表进行删，改操作.（需要等待锁释放才能操作...）
在Repeatable read隔离级别下不能对锁住行进行增、删、改操作.（需要等待锁释放才能操作...）
在Serializable隔离级别下不能对锁住行进行增、删、改操作. （需要等待锁释放才能操作...）
8.在同一个会话中的可以叠加多个共享锁和排他锁.在多个会话中,需要等待锁的释放.
9.SQL中的update 与 for update是一样的原理.
10.等待超时的参数设置:innodb_lock_wait_timeout=50 (单位秒).
11.任何可以触发事务提交的命令,都可以关闭共享锁和排它锁.
Java如何实现对Mysql数据库的行锁
下面通过一个例子来说明
场景如下：
用户账户有余额，当发生交易时，需要实时更新余额。这里如果发生并发问题，那么会造成用户余额和实际交易的不一致，这对公司和客户来说都是很危险的。
那么如何避免：
网上查了下，有以下两种方法：
1、使用悲观锁
当需要变更余额时，通过代码在事务中对当前需要更新的记录设置for update行锁，然后开始正常的查询和更新操作
这样，其他的事务只能等待该事务完成后方可操作
当然要特别注意，如果使用了Spring的事务注解，需要配置一下：
!-- (事务管理)transaction manager, use JtaTransactionManager for global tx --
bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
property name="dataSource" ref="dataSource" /
/bean
!-- 使用annotation定义事务 --
tx:annotation-driven transaction-manager="transactionManager" /
在指定代码处添加事务注解
@Transactional
@Override
public boolean increaseBalanceByLock(Long userId, BigDecimal amount)
throws ValidateException {
long time = System.currentTimeMillis();
//获取对记录的锁定
UserBalance balance = userBalanceDao.getLock(userId);
LOGGER.info("[lock] start. time: {}", time);
if (null == balance) {
throw new ValidateException(
ValidateErrorCode.ERRORCODE_BALANCE_NOTEXIST,
"user balance is not exist");
}
boolean result = userBalanceDao.increaseBalanceByLock(balance, amount);
long timeEnd = System.currentTimeMillis();
LOGGER.info("[lock] end. time: {}", timeEnd);
return result;
}
MyBatis中的锁定方式，实际测试该方法确实可以有效控制，不过在大并发量的情况下，可能会有性能问题吧
select id="getLock" resultMap="BaseResultMap" parameterType="java.lang.Long"
![CDATA[
select * from user_balance where id=#{id,jdbcType=BIGINT} for update;
]]
/select
2、使用乐观锁
这个方法也同样可以解决场景中描述的问题（我认为比较适合并不频繁的操作）：
设计表的时候增加一个version（版本控制字段），每次需要更新余额的时候，先获取对象，update的时候根据version和id为条件去更新，如果更新回来的数量为0，说明version已经变更
需要重复一次更新操作，如下：sql脚本
update user_balance set Balance = #{balance,jdbcType=DECIMAL},Version = Version+1 where Id = #{id,jdbcType=BIGINT} and Version = #{version,jdbcType=BIGINT}
这是一种不使用数据库锁的方法，解决方式也很巧妙。当然，在大量并发的情况下，一次扣款需要重复多次的操作才能成功，还是有不足之处的。不知道还有没有更好的方法。
