sql的for update

 

oracle行级共享锁

通常是通过select … from for update语句添加的，同时该方法也是我们用来手工锁定某些记录的主要方法。比如，当我们在查询某些记录的过程中，不希望其他用户对查询的记录进行更新操作，则可以发出这样的语句。当数据使用完毕以后，直接发出rollback命令将锁定解除（如果我们查询到了并且修改了，则commit后就可以释放了锁）。当表上添加了RS锁定以后，不允许其他事务对相同的表添加排他锁，但是允许其他的事务通过DML语句或lock命令锁定相同表里的其他数据行。

select for update

示例：

select *from goods where goods_code ='9990300100000030001' for update ;

当select语句中使用了for update，如果当前select发现自己的结果集中有一条或者多条数据正在被修改（如有其他的语句提前for update了等），那么再执行此语句就会一直等待：

此时执行：

Update/delete等语句都出现等待。只有对应的占用锁的数据被提交commi（或者rollback）t掉时才可以进行新的for update 上锁或者进行update、delete。

select for update nowait

for update nowait 和for  update都会对查询到的当前结果集进行加锁。区别是是for update nowait 发现自己要加锁的结果集正在被其他操作修改（已经被加锁），则直接反馈资源占用，不会像for update 一样等待执行。

这两个操作等待和弹窗与被那种形式的加锁sql无关，只要要处理的结果集被加锁了直接反馈弹窗。（应该是废话）

select for update wait

与for update nowait的区别是for update wait 可配置等待时间，单位秒。当过了这个时间，被加锁资源还未被释放，则和for update nowait一样直接弹窗反馈资源占用。否则执行此语句。

跳过加锁行，对满足条件的其他行进行加锁

步骤：

窗口1（每个窗口代表一个session）执行

select *from goods where goods_code ='9990300100000030001' for update  ;

窗口2中执行：

由于窗口2的sql集合中有一行被窗口1的集合加锁了，导致窗口二等待锁释放。

窗口2 修改语句如下：

select *from goods for update skip locked ;

关联表加锁

1.关联的表都加锁锁

select *

  from stat_dic_goods sg

 inner join goods g

    on sg.goods_code = g.goods_code

 where g.goods_code = 'qwe002n3'

   for update;

可以得出结论两张表中goods_code='qwe002n3'的商品都被加锁了，所以导致后免得语句对两张整体加锁时出现了等待的情况。

 

当在新开的窗口中对其他数据进行加锁时加锁成功：

2. 对关联的表中的某一张表进行加锁

 

select *
  from stat_dic_goods sg
 inner join goods g
    on sg.goods_code = g.goods_code
 where g.goods_code = 'qwe002n3'
   for update of sg.goods_code;

 

Oracle共享锁

语法：lock table stat_dic_goods in share mode;

执行完lock语句再执行update语句就会出现等待中，因为lock语句已经将这个表锁了，此时只允许读取不允许修改。

通过lock table in share mode命令添加该S锁。在该锁定模式下，不允许任何用户更新表。但是允许其他用户发出select …from for update命令对表添加RS锁。

 

 

Oracle只有表级别的共享锁没有行级别的共享锁，行级别的是排他锁，多个用户可以同时对同一个表加共享锁。通过下面的语句就可以看出来。

Oracle排他锁

示例语句：lock table stat_dic_goods in exclusive mode;

同一张表不能同时执行共享锁和排他锁。

通过lock table in exclusive mode命令添加X锁。在该锁定模式下，其他用户不能对表进行任何的DML和DDL操作，该表上只能进行查询。

Oracle行级排他锁

当我们进行DML时会自动在被更新的表上添加RX锁，或者也可以通过执行lock命令显式的在表上添加RX锁。在该锁定模式下，允许其他的事务通过DML语句修改相同表里的其他数据行，或通过lock命令对相同表添加RX锁定，但是不允许其他事务对相同的表添加排他锁（X锁）。

参考了这里：http://www.cnblogs.com/hsz1124/p/7409981.html

找了相关资料理解的还不是懂

Oracle共享行级排他锁

通过lock table in share row exclusive mode命令添加SRX锁。该锁定模式比行级排他锁和共享锁的级别都要高，这时不能对相同的表进行DML操作，也不能添加共享锁。

找了相关资料理解的还不是懂

 

TM锁，表级锁；

TX（事务）锁；

lock只会给表上锁（TM锁），select for update，insert，delete，update既给表上锁（TM锁），也给行上锁（TX锁）。select for update给表上行共享锁，insert，delete，update给表上行排他锁。

数据库的隔离级别

回头看看数据库的隔离级别：

隔离级别就是对事务并发控制的等级。ANSI/ ISO SQL将其分为串行化（SERIALIZABLE）、可重复读（REPEATABLE READ）、读已提交（READ COMMITED）、读未提交（READ UNCOMMITED）四个等级。为了实现隔离级别通常数据库采用锁（Lock）。一般在编程的时候只需要设置隔离等级，至于具体采用什么锁则由数据库来设置。

Oracle: Read Commited

MySQL : Repeatable Read

Oracle默认情况下，读数据不加锁，而是通过回滚段防止脏读和保证可重复读。

Oracle具有死锁检查功能，周期性检查系统是否有死锁，如果存在死锁，则撤销执行更新操作次数最少的事务。

 

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！要是对应的SQL语句没有走索引，则会锁掉整张表。

 

Mysql

Mysql只有在InnoDB引擎下才支持行锁和表锁。Mysql的默认引擎就是innoDB。

MySQL默认操作模式就是autocommit自动提交模式（值为1）。这就表示除非显式地开始一个事务，否则每个查询都被当做一个单独的事务自动执行。我们可以通过设置autocommit的值改变是否是自动提交autocommit模式。

可以手动设置：set autocommit = 0;则将自动提交关闭了。

示例：

悲观锁：

begin;

select * from goods where id = 1 for update;

update goods set stock = stock - 1 where id = 1;

commit;

 

乐观锁：（已改造）

#不加锁获取 id=1 的商品对象

select * from goods where id = 1

begin;

#更新 stock 值，这里需要注意 where 条件 “stock = cur_stock”，只有程序中获取到的库存量与数据库中的库存量相等才执行更新

update goods set stock = stock - 1 where id = 1 and stock = cur_stock;

commit;

程序中大多用的是乐观锁，悲观锁

 

与oracle差异：

Mysql支持：select * from orders for update wait 6 –语法错误

                  select * from orders for update nowait 6 ; –语法错误

还没有总结完，后续补充

项目组用的jpa。谢谢jpa的配置吧

@Repository 
public interface JobInfoDao extends JpaRepository<JobInfo, Long> {


@Query(value = "select j from JobInfo j where j.jobName = :jobname ") 
public JobInfo getJobForUpdate(@Param("jobname") String jobname); 


@Lock(value = LockModeType.PESSIMISTIC_WRITE) 
@Query(value = "select j from JobInfo j where j.id = :id ") 
public void getJobByIdForUpdate(@Param("id") Long id); 

 
@Lock(value = LockModeType.PESSIMISTIC_WRITE) // TODO 千万不要用这个哦！ 
@Query(value = "select j from JobInfo j where j.jobName = :jobname ") 
public void getJobByNameForUpdate(@Param("jobname") String jobname); 


} 

 

.jpa如何搞 select for update。

也是醉了，在Dao层的方法上，要配置Lock的注解。并且要加上LockModeType.PESSIMISTIC_WRITE ，这个就相当于for update了。大家也可以在程序运行时，打印出的sql中看到。 

 

ALTER TABLE pay_bill
CHANGE COLUMN `created_time` `created_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;

ALTER TABLE  order
CHANGE COLUMN `created_time` `created_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;

ALTER TABLE refund
CHANGE COLUMN `created_time` `created_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;