-- Oracle
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> create table t1(c11 integer, c12 integer);
Table created.
SQL> create table t2(c21 integer, c22 integer);
Table created.
SQL> insert into t1 values(1,1);
1 row created.
SQL> insert into t1 values(2,2);
1 row created.
SQL> insert into t2 values(1, 1);
1 row created.
SQL> insert into t2 values(2, 2);
1 row created.
SQL> commit;
Commit complete.
-- session a
SQL> select * from t1 where c11 = 1 for update;
C11 C12
---------- ----------
1 1
-- session b
SQL> update t1 set c12 = 2 where c11 = 1;
...
EN ATTENTE !
-- session c
SQL> update t1 set c11 = 3 where c11 = 2;
1 row updated.
-- PostgreSQL
select version();
version
---------------------------------------------------------------------------------------------------
PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.2.0-10) 6.2.0 20161027, 64-bit
(1 ligne)
create table t1(c11 integer, c12 integer);
CREATE TABLE
create table t2(c21 integer, c22 integer);
CREATE TABLE
insert into t1 values(1,1);
INSERT 0 1
insert into t1 values(2,2);
INSERT 0 1
insert into t2 values(1, 1);
INSERT 0 1
insert into t2 values(2, 2);
INSERT 0 1
-- session a
start transaction;
START TRANSACTION
select * from t1 where c11 = 1 for update;
c11 | c12
-----+-----
1 | 1
(1 ligne)
-- session b
start transaction;
START TRANSACTION
update t1 set c12 = 2 where c11 = 1;
...
EN ATTENTE !
-- session c
start transaction;
START TRANSACTION
update t1 set c11 = 3 where c11 = 2;
UPDATE 1
-- annulation de tout le travail
-- Oracle
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-- session a
select * from t1 where c11 = 1 for update of t1;
select * from t1 where c11 = 1 for update of t1
*
ERROR at line 1:
ORA-00904: "T1": invalid identifier
SQL> select * from t1 where c11 = 1 for update of t1.c11;
C11 C12
---------- ----------
1 1
-- session b
SQL> update t1 set c12 = 2 where c11 = 1;
...
EN ATTENTE !
-- session c
SQL> update t1 set c11 = 3 where c11 = 2;
1 row updated.
-- PostgreSQL
select version();
version
---------------------------------------------------------------------------------------------------
PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.2.0-10) 6.2.0 20161027, 64-bit
(1 ligne)
-- session a
start transaction;
START TRANSACTION
select * from t1 where c11 = 1 for update of t1.c11;
ERREUR: FOR UPDATE doit indiquer les noms de relation non qualifiés
LIGNE 1 : select * from t1 where c11 = 1 for update of t1.c11;
rollback;
ROLLBACK
start transaction;
START TRANSACTION
select * from t1 where c11 = 1 for update of t1;
c11 | c12
-----+-----
1 | 1
(1 ligne)
-- session b
start transaction;
START TRANSACTION
update t1 set c12 = 2 where c11 = 1;
...
EN ATTENTE !
-- session c
start transaction;
START TRANSACTION
update t1 set c11 = 3 where c11 = 2;
UPDATE 1
-- annulation de tout le travail
-- Oracle
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
-- session a
SQL> select * from t1 join t2 on (t1.c11 = t2.c21) where t1.c11 = 1 for update of t1.c11;
C11 C12 C21 C22
---------- ---------- ---------- ----------
1 1 1 1
-- session b
SQL> update t1 set c12 = 2 where c11 = 1;
...
EN ATTENTE !
-- session c
SQL> update t2 set c22 = 2 where c21 = 1;
1 row updated.
-- PostgreSQL
select version();
version
---------------------------------------------------------------------------------------------------
PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.2.0-10) 6.2.0 20161027, 64-bit
(1 ligne)
-- session a
start transaction;
START TRANSACTION
select * from t1 join t2 on (t1.c11 = t2.c21) where t1.c11 = 1 for update of t1;
c11 | c12 | c21 | c22
-----+-----+-----+-----
1 | 1 | 1 | 1
(1 ligne)
-- session b
start transaction;
START TRANSACTION
update t1 set c12 = 2 where c11 = 1;
...
EN ATTENTE !
-- session c
start transaction;
START TRANSACTION
update t2 set c22 = 2 where c21 = 1;
UPDATE 1