select ...for update 和insert all/first

最新推荐文章于 2023-04-22 09:30:50 发布
最新推荐文章于 2023-04-22 09:30:50 发布
阅读量522 收藏
点赞数
分类专栏： 个人经验总结 文章标签： 数据库 11gR2 函数

Oracle中select ... for update的用法

 

语法：

SELECT ... FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED];

其中OF 子句用于指定即将更新的列，即锁定行上的特定列； WAIT 子句指定等待其他用户释放锁的秒数，防止无限期的等待。

使用“FOR UPDATE WAIT”子句的优点如下：

1防止无限期地等待被锁定的行；

2允许应用程序中对锁的等待时间进行更多的控制。

3对于交互式应用程序非常有用，因为这些用户不能等待不确定

4若使用了skip locked，则可以越过锁定的行，不会报告由wait n引发的‘资源忙’异常报告

 

实验：

create table t(a varchar2(20),b varchar2(20));

insert into t values('1','1');

insert into t values('2','2');

insert into t values('3','3');

insert into t values('4','4');

insert into t values('5','5');

insert into t values('6','6');

 

（1）在PLSQL Developer中打开两个窗口，在窗口1中执行

select * from t where a='1' for update;

结果如下


 

在窗口2中执行

select * from t where a='1';

结果如下


可见此时能正常查询。

 

在窗口2中执行

select * from t where a='1' for update;

发现无法查询出结果且PLSQL Developer的执行按钮一直为灰色。

这是因为表被窗口1里的语句锁住了，窗口2处于等待状态。

只有等窗口1中提交了事务之后才能在窗口2中正常执行上述语句。

在窗口1中点击提交事务的按钮后，窗口2中立马显示出正常结果

 

把窗口1和2中未提交的事务都提交，以便进行下一步的实验。

 

（2）

在窗口1中执行select * from t where a='1' for update;

在窗口2中执行select * from t where a='1' for update nowait;

立马报资源正忙的错误：

关掉上面的错误提示窗口，在窗口2中执行

select * from t where a='1' for update wait 6;

则6秒之后报错：


 

关掉上面的错误提示，在窗口2中执行

select * from t where a='1' for update skip locked;

则既不等待，也不报错，也查询不出结果：

把窗口1和2中未提交的事务都提交，以便进行下一步的实验。

 

（3）

在窗口1中执行：

select * from t where rownum<=3 for update skip locked;

结果如下：


 

在窗口2中执行： 
select * from t where rownum<=6 for update skip locked;

结果如下：


可见前三条数据因被窗口1锁住而没有查出来。；





------------------------------------------------------------------------------------------------------------.

INSERT ALL是9i新增的语法，它扩充了原有的INSERT语句，使得INSERT语句从原来的只能插入到一张表发展到可以同时插入多张表，还可以根据判断条件来决定每条记录插入到哪张或哪几张表中。

下面看几个简单的例子：

SQL> CREATE TABLE TABLE_STORAGE 
  2  (
  3  TABLE_NAME VARCHAR2(30), 
  4  TABLESPACE_NAME VARCHAR2(30), 
  5  PCT_FREE NUMBER, 
  6  PCT_USED NUMBER, 
  7  INI_TRANS NUMBER, 
  8  MAX_TRANS NUMBER, 
  9  INITIAL_EXTENT NUMBER, 
 10  NEXT_EXTENT NUMBER, 
 11  MIN_EXTENTS NUMBER, 
 12  MAX_EXTENTS NUMBER, 
 13  PCT_INCREASE NUMBER, 
 14  FREELISTS NUMBER, 
 15  FREELIST_GROUPS NUMBER
 16  );

表已创建。

SQL> CREATE TABLE TABLE_STAT 
  2  (
  3  TABLE_NAME VARCHAR2(30), 
  4  NUM_ROWS NUMBER, 
  5  BLOCKS NUMBER, 
  6  EMPTY_BLOCKS NUMBER, 
  7  AVG_SPACE NUMBER, 
  8  CHAIN_CNT NUMBER, 
  9  AVG_ROW_LEN NUMBER
 10  );

表已创建。

SQL> INSERT ALL
  2  INTO TABLE_STORAGE VALUES (TABLE_NAME, TABLESPACE_NAME, PCT_FREE, PCT_USED, 
  3  INI_TRANS, MAX_TRANS, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, 
  4  PCT_INCREASE, FREELISTS, FREELIST_GROUPS)
  5  INTO TABLE_STAT VALUES (TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, 
  6  CHAIN_CNT, AVG_ROW_LEN)
  7  SELECT * FROM USER_TABLES;

已创建54行。

SQL> SELECT COUNT(*) FROM TABLE_STORAGE;

  COUNT(*)
----------
        27

SQL> SELECT COUNT(*) FROM TABLE_STAT;

  COUNT(*)
----------
        27

SQL> SELECT COUNT(*) FROM USER_TABLES;

  COUNT(*)
----------
        27

SQL> DROP TABLE TABLE_STAT;

表已丢弃。

SQL> DROP TABLE TABLE_STORAGE;

表已丢弃。

上面是最简单的INSERT ALL语句的实现，下面看看带条件的INSERT ALL语句。

SQL> CREATE TABLE TABLE_ALL (TABLE_NAME VARCHAR2(30));

表已创建。

SQL> CREATE TABLE INDEX_ALL (INDEX_NAME VARCHAR2(30));

表已创建。

SQL> CREATE TABLE OBJECT_OTHER (OBJECT_NAME VARCHAR2(30), OBJECT_TYPE VARCHAR2(30));

表已创建。

SQL> INSERT ALL
  2  WHEN (OBJECT_TYPE = 'TABLE') THEN
  3  INTO TABLE_ALL VALUES (OBJECT_NAME)
  4  WHEN (OBJECT_TYPE = 'INDEX') THEN
  5  INTO INDEX_ALL VALUES (OBJECT_NAME)
  6  ELSE
  7  INTO OBJECT_OTHER 
  8  SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS 
  9  ;

已创建91行。

SQL> SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE';

  COUNT(*)
----------
        27

SQL> SELECT COUNT(*) FROM USER_OBJECTS WHERE OBJECT_TYPE = 'INDEX';

  COUNT(*)
----------
        14

SQL> SELECT COUNT(*) FROM USER_OBJECTS 
  2  WHERE OBJECT_TYPE NOT IN ('TABLE', 'INDEX');

  COUNT(*)
----------
        50

SQL> SELECT COUNT(*) FROM TABLE_ALL;

  COUNT(*)
----------
        27

SQL> SELECT COUNT(*) FROM INDEX_ALL;

  COUNT(*)
----------
        14

SQL> SELECT COUNT(*) FROM OBJECT_OTHER;

  COUNT(*)
----------
        50

SQL> DROP TABLE TABLE_ALL;

表已丢弃。

SQL> DROP TABLE INDEX_ALL;

表已丢弃。

SQL> DROP TABLE OBJECT_OTHER;

表已丢弃。

下面看一下INSERT ALL和INSERT FIRST的区别：

SQL> CREATE TABLE TABLESPACE_USERS (NAME VARCHAR2(30), TYPE VARCHAR2(30));

表已创建。

SQL> CREATE TABLE TABLE_ALL (TABLE_NAME VARCHAR2(30));

表已创建。

SQL> INSERT ALL
  2  WHEN (SEGMENT_TYPE = 'TABLE') THEN
  3  INTO TABLE_ALL VALUES (SEGMENT_NAME)
  4  WHEN (TABLESPACE_NAME = 'USERS') THEN
  5  INTO TABLESPACE_USERS VALUES (SEGMENT_NAME, SEGMENT_TYPE)
  6  SELECT SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME 
  7  FROM USER_SEGMENTS;

已创建69行。

SQL> SELECT COUNT(*) FROM TABLE_ALL;

  COUNT(*)
----------
        21

SQL> SELECT COUNT(*) FROM TABLESPACE_USERS;

  COUNT(*)
----------
        48

SQL> SELECT COUNT(*) FROM TABLESPACE_USERS WHERE TYPE = 'TABLE';

  COUNT(*)
----------
        18

SQL> TRUNCATE TABLE TABLE_ALL;

表已截掉。

SQL> TRUNCATE TABLE TABLESPACE_USERS;

表已截掉。

SQL> INSERT FIRST
  2  WHEN (SEGMENT_TYPE = 'TABLE') THEN
  3  INTO TABLE_ALL VALUES (SEGMENT_NAME)
  4  WHEN (TABLESPACE_NAME = 'USERS') THEN
  5  INTO TABLESPACE_USERS VALUES (SEGMENT_NAME, SEGMENT_TYPE)
  6  SELECT SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME 
  7  FROM USER_SEGMENTS;

已创建51行。

SQL> SELECT COUNT(*) FROM TABLE_ALL;

  COUNT(*)
----------
        21

SQL> SELECT COUNT(*) FROM TABLESPACE_USERS;

  COUNT(*)
----------
        30

SQL> SELECT COUNT(*) FROM TABLESPACE_USERS WHERE TYPE = 'TABLE';

  COUNT(*)
----------
         0

插入过的数据不再其他表中在次插入


----限制

最后看一下多表插入语句的限制条件：

只能对表执行多表插入语句，不能对视图或物化视图执行；

不能对远端表执行多表插入语句；

不能使用表集合表达式；

不能超过999个目标列；

在RAC环境中或目标表是索引组织表或目标表上建有BITMAP索引时，多表插入语句不能并行执行；

多表插入语句不支持执行计划稳定性；

多表插入语句中的子查询不能使用序列。


【0153】深入分析postgres接收libpq诸如insert、delete、update、select . . . 等命令的实现原理（1）
Postgres 数据库内核开发工程师
12-11 762
将Port数据类型中的sock设置为阻塞模式，其实socket_set_nonblocking()函数并非真正设置sock文件描述的阻塞/非阻塞属性，只是设置了与之管理的Port结构中的noblock成员，该成员与sock是否为阻塞属性描述是一一对应关系。当postgres进程创建成功之后，会有一个死循环。如果消息太长，无法装入所提供的缓冲区，则可能会丢弃多余的字节，这取决于接收消息的套接字类型。如果套接字上没有消息可用，则接收调用等待消息到达，除非套接字是非阻塞的(参见fcntl(2))，
Oracle只读用户执行SELECT FOR UPDATE导致锁表
最新发布
Sebastien23的博客
08-28 599
READER24用户对表 RDR_NPC 执行 SELECT FOR UPDATE 操作后，既不提交也不回滚。READER24退出会话后，APPUSER1的更新和删除操作才能继续执行。READER24提交会话后，APPUSER1的更新和删除操作也能继续执行。只读用户READER24执行 UPDATE 报错后，退出会话。检查发现APPUSER1会话有行锁等待。
聊聊select for update到底加了什么锁
m0_71777195的博客
01-11 2211
最近在开发需求的时候，用到了。在代码评审的时候，一位同事说 ，唯一索引+一个非索引字段，是否可能会锁全表呢？本文田螺哥将通过9个实验操作的例子，给大家验证到底加了什么锁，是表锁还是行锁。这是本文的提纲哈：因为加锁是跟数据库的隔离级别息息相关的。而常用的数据库隔离级别也就RC(读已提交)和RR(可重复读)，所以本文分别根据RC(读已提交) 和 RR(可重复读)隔离级别展开讲述。我是捡田螺的小男孩。通过本文，大家学到哪些知识点呢？在不同场景，都加了什么锁。
[MSSQL]SELECT INTO 和 INSERT INTO SELECT 两种表复制语句
php|nginx|python|vue|redis|linux
12-08 8351
[MSSQL]SELECT INTO 和 INSERT INTO SELECT 两种表复制语句2008-12-03 10:37Insert是T-sql中常用语句，Insert INTO table(field1,field2,...) values(value1,value2,...)这种形式的在应用程序开发中必不可少。但我们在开发、测试过程中，经常会遇到需要表复制
SELECT语句中的for update的用法
04-07 2958
转自：http://shenzhenchufa.blog.51cto.com/730213/153818
mysql的insert for update用法
奋斗吧,CQS!
03-24 1万+
好久没写博客了，洗完澡一时兴起，小写一篇记录sql知识点吧。 由于之前公司业务的变更，需要做一个历史数据表数据迁移到新建的数据表中，刚被分配到这个任务时，马上打开历史数据表的相关结构，一看懵逼了。需求大概是把原来的几百张数据库表（里面大概有三四类表，由于数据量太大，都进行一定规则的分表）迁移到新建的数据库表（也是采用一定规则分表）。 第一阶段，采用insert语法批量添...
mysql根据select 结果insert
美好清新
05-13 2万+
INSERT INTO peiyou.`py_payment_log` ( order_code, pay_code, user_id, channel, payment_code, payment_name, need_pay, pay_return_value, pay_notify_value, pay_status, MID, tid, ...
first_select.rar_The First
09-24
3. SqlCommand对象：用于执行SQL命令，如SELECT、INSERT、UPDATE和DELETE。 4. SqlDataReader对象：用于从数据库中读取数据，提供高效的数据流读取方式。 5. SqlDataAdapter对象和DataSet对象：用于填充数据集，...
【快速了解】数据库CREATE|INSERT INTO|DELETE|UPDATE|SELECT|DROP|RENAME|ALTER...
HuanLe.的博客
08-01 1170
常用到的基础数据库语句
浅谈PL/SQL批处理语句:BULK COLLECT与FORALL对优化做出的贡献
09-10
FORALL i IN ids.FIRST..ids.LAST INSERT INTO departments (department_id) VALUES (ids(i)); COMMIT; END; ``` 此示例中，FORALL 会一次性插入三个部门，而不是三次单独的INSERT操作。 总结起来，BULK COLLECT...
select ... for update
weixin_34242658的博客
11-20 108
通常，select ... for update是为了锁定相关的行，保证在查询期间到释放的时候，相关的行集在这个过程中不被其他会话进行写操作（但是其他行集却并无影响）。比如，有表：create table t ( id number);insert into t select object_id from dba_objects where object_id<=100...
利用select直接insert数据
跑码场
10-14 3657
同时CREATE、SELECT、INSERT的三种方法AS能把SELECT查询的结果填入到新的表格中1、CREATE TABLE，然后利用SELECT进行INSERTCREATE TABLE profession( id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, profession VARCHAR(20) ); //现在以SELECT
将select的结果全部插入insert表中
weixin_43862793的博客
06-03 1万+
有时候我们需要将select出的结果用sql的方式全部insert到一个新的表中 INSERT into test (no,name) select s.degree,s.sno from scores s 执行之前 执行之后 需要注意的是，在进行将select结果批量insert时，字段名称可以不一样，但是插入数量和查询数量要一致，他将会按照一一匹配去进行插入，也就是你insert时写的第一个插入字段，会把你select出来的第一个字段的值进行插入 ...
mysql:使用select查出的数据进行批量insert
Evelyn_Jone的博客
10-25 6383
A,B的表结构 A:a,b,c B:a,b,c 需求 将B表中的a，c字段插入A表中，其中A表的b字段默认为’b’. code insert into A (a,b,c) select s.a,'b',s.c from ( select t.a,t.c from B t ) s
Mysql数据库进阶之select for update(五)
xiaotu的博客
04-22 9246
select for update 所实现的效果就是：一旦 A 事务中执行了 select for update ，那么，在 A 事务结束（提交或回滚）之前，B 事务就无法操作 A 事务的 select for update 所涉及的这条数据，哪怕是去查也不行，直到 A 事务提交之后才可以。
SQL中 SELECT INSERT 最简单使用
老菜鸟
05-17 6057
SQL中 SELECT INSERT 最简单使用  (2012-03-30 17:34:43) 转载▼ 标签：  杂谈   首先我们创建一个数据库为march_30   创建一个表students  并且有主键 ID 名字name 年龄age 地址 address  并且，名字不允许为空，其他都可以。   C
常用mysql语句-select直接insert/update
zzlu_suran的博客
10-10 1230
select直接insert/update，CASE WHEN THEN END，UNION ALL，分组统计，Truncated incorrect DOUBLE value
数据库中Select For update语句的解析
热门推荐
wblearn的博客
10-25 4万+
——————————— Oracle —————————————————– Oracle 的for update行锁键字: oracle 的for update行锁 　　SELECT…FOR UPDATE 语句的语法如下： 　　SELECT … FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED]; 其中： 　　OF 子
select ... for update 语句的分析
860MHz的专栏
01-30 1753
1. 前言 本文将介绍select … for update语句相关知识，以及对可能导致的死锁和影响进行分析。 如无特殊说明，本文涉及的MySQL版本为8.0.22，事务隔离级别为RR，数据库引擎为INNODB。 2. 实验数据准备 创建一个测试表t_a： CREATE TABLE `test_a` ( `id` int NOT NULL, `a` int NOT NULL, `b` int NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAU
SQL入门：基本的Insert、Select、Update和Delete操作
在SQL中，Insert、Select、Update和Delete是四大基本的DML语句，用于对数据库进行增、删、改、查操作。文章通过实例展示了如何使用这些语句来操作数据库中的数据，特别是对一个名为EMPLOYEES的表进行插入记录的演示...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值