SQL: чудеса с апдейтом

[ALV00]

Code: Select all

SQL> select * from temp5 order by 1;

        C1         C2
---------- ----------
         1          0
         2          0
         3          0
         4          0

SQL> update temp5 a
  2  set c2 = (
  3      select c2+1
  4      from temp5 b
  5      where b.c1 = a.c1-1
  6  );

4 rows updated.

SQL> select * from temp5 order by 1;

        C1         C2
---------- ----------
         1
         2          1
         3          1
         4          1

ожидалось:
        C1         C2
---------- ----------
         1
         2          1
         3          2
         4          3

Почему такое происходит и как с этим бороться? База Oracle 12c, если это имеет значение.

[ALV00]

Короче, смысл в том, чтобы читать записи только что проапдейтенные в той же таблице в той же транзакции. Oracle не дает такой возможности что ли?

[mskmel]

Короче, смысл в том, чтобы читать записи только что проапдейтенные в той же таблице в той же транзакции. Oracle не дает такой возможности что ли?

Читать что такое транзакция вообще и что такое https://en.wikipedia.org/wiki/Consisten ... e_systems) в частности.
Oracle тут совсем не при чём, он отрабатывает как ожидается по стандартам.

[ALV00]

Похоже, что транзакции тут ни при чем. Сделал курсор с коммитом после каждого чиха, та же история. Оракл не дает одновременно писать и читать из одной таблицы. Поправка: некорректно читает только что проапдейченную запись.

[mskmel]

Сделал курсор с коммитом после каждого чиха, та же история

Это нормально, продолжайте наблюдение.

Детальнее: курсор будет всегда читать данные как они выглядели на момент его открытия (есть разные "если"). Если хотите того, чего хотите - открывайте курсор после каждого изменения, commit делать не обязательно.

[iDesperado]

так и должно быть. почитай https://oracle-base.com/articles/misc/l ... -functions
скорее всего тебе куда-то в сторону LAG нужно копать

[Easbayguy]

Code: Select all

SQL> select * from temp5 order by 1;

        C1         C2
---------- ----------
         1          0
         2          0
         3          0
         4          0

SQL> update temp5 a
  2  set c2 = (
  3      select c2+1
  4      from temp5 b
  5      where b.c1 = a.c1-1
  6  );

4 rows updated.

SQL> select * from temp5 order by 1;

        C1         C2
---------- ----------
         1
         2          1
         3          1
         4          1

ожидалось:
        C1         C2
---------- ----------
         1
         2          1
         3          2
         4          3

Почему такое происходит и как с этим бороться? База Oracle 12c, если это имеет значение.

В принципе, если просто задача пронумеровать последовательно записи по ключу начиная с 0 и учесть возможные дырки в C1 то будет вот так.

MERGE INTO (SELECT c1,c2
FROM temp5) a
USING (select c1, row_number() over(order by c1 asc) as rn
from temp5) b
ON (a.c1 = b.c1)
WHEN MATCHED THEN UPDATE SET a.c2 = b.rn-1;

Конечно, если у вас табличка уже отсортирована по ключу c1, то вообще красиво!
update temp5 set c2=rownum-1;

[Easbayguy]

Похоже, что транзакции тут ни при чем. Сделал курсор с коммитом после каждого чиха, та же история. Оракл не дает одновременно писать и читать из одной таблицы. Поправка: некорректно читает только что проапдейченную запись.

Просто привыкните что для одного update все записи от первой до последней находятся в том же состоянии как для первой считанной записи. Та же байда с курсором.

[ALV00]

В принципе, если просто задача пронумеровать последовательно записи по ключу начиная с 0 и учесть возможные дырки в C1 то будет вот так.

То, что я написал, это упрощение. Там несколько более сложная задача и нужно именно читать только что измененные данные. Ну понятно, на каждый отдельный апдейт курсор открывается. Не знал...

[Easbayguy]

В принципе, если просто задача пронумеровать последовательно записи по ключу начиная с 0 и учесть возможные дырки в C1 то будет вот так.

То, что я написал, это упрощение. Там несколько более сложная задача и нужно именно читать только что измененные данные. Ну понятно, на каждый отдельный апдейт курсор открывается. Не знал...

Немного не понимаю в чем проблема, вот пожалуста, читайте измененные данные:

select * from temp5 order by 1;
declare
v_c2 integer;
begin
for j in (select rowid row_id, c1, c2 from temp5) loop
begin
select c2+1 into v_c2 from temp5 b where b.c1 = j.c1-1;
exception
when NO_DATA_FOUND then
v_c2:=0;
end;
update temp5 set c2=v_c2 where rowid=j.row_id;
end loop;
commit;
end;
/
select * from temp5 order by 1;


C1 C2
--------------- ---------------
1 0
2 0
3 0
4 0
5 0

Elapsed: 00:00:00.01

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

C1 C2
--------------- ---------------
1 0
2 1
3 2
4 3
5 4

[ALV00]

Но с другой стороны:

Code: Select all

SQL> begin
  2      for j in (
  3          select c1 from temp5 order by c1
  4      )
  5      loop
  6          update temp5 a
  7          set c2 = (
  8              select b.c2+1
  9              from temp5 b
 10              where b.c1 = j.c1 - 1
 11          )
 12          where a.c1 = j.c1;
 13      end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL> select * from temp5 order by 1;

        C1         C2
---------- ----------
         1
         2
         3
         4
         5
         6
         

Если сначала делать селект в переменную, потом из нее апдейтить, то работает. А если совместить апдейт с селектом, то нет. Хз как оно устроено.
Есть ли какой-то способ посмотреть байт код, который генерится из PL/SQL, типа того, что выдает javap для JVM?

[ALV00]

Если почитать документацию, то можно узнать про "select for update". Хотя я, если честно, не понимаю, что вы хотите сделать.
Насчет "посмотреть код" : существует пошаговый отладчик. Можно конечно использовать отладчик из sqlplus , но не очень удобно. Отладчик (дебаггер) встроен во все более менее популярные средства разработки типа PL/SQL Developer, SQL Developer .

Я хочу шагать по таблице курсором, апдейтить текущую запись, одновременно считывая данные из той же таблицы, возможно уже измененные в том же курсоре. То есть как если бы я работал с массивом в памяти. Но она почему то не считывает новые данные, а берет старые. Транзакции и локи тут ни при чем. Я в одной сессии, читаю и пишу мои данные, другие юзеры никак не могут помешать, мне же Оракл обеспечивает полную изоляцию. Непонятно такое поведение.

[mskmel]

Непонятно такое поведение.

Ещё чтиво на тему:
https://en.wikipedia.org/wiki/Snapshot_isolation
snapshot isolation is a guarantee that all reads made in a transaction will see a consistent snapshot of the database (in practice it reads the last committed values that existed at the t
ime it started)

[iDesperado]

"Вот где собака порылась" (ц не мой). В Оракле читатели не мешают писателям, а писатели не мешают читателям. В вашем коде вы не запрещаете другим пользователям изменять данные .

Почитайте здесь, поможет : https://docs.oracle.com/database/121/CN ... #CNCPT1115

не, чувак просто не понимает где у него SQL statement, а где микс декларативного SQL и итеративного PL/SQL. ну и явно не понимает что такое декларативный язык. он считает что как в жава, в какой последовательности он проинсертил, в такой последовательности стейтмент обязан считывать и считать c2, не задумываясь о декларативности.

к стате, в этом плане интересно как в блокировочниках. мне кажется там должно быть именно так как товарищ ожидает. @Dima в мсскл что будет после апдейта из первого поста и как это согласуется с декларативностью скл ? выходит кластерный индекс может изменить результат апдейта ?

UPDATE: не, вроде и мсскл все правильно и так же как оракл делает. хотя интересно как он это делает в блокировочном режиме
мой тест: https://rextester.com/ZRASA57638

[ALV00]

Lazy, вы совсем не врубились в тему. У меня не просто select for update, a update set ...= select из той же таблицы других записей ея.

Snapshot isolation это тоже несколько о другом. Этот механизм гарантирует, что я из курсора прочитаю только то, что было в таблице на момент его открытия, даже если там что-то другой юзер поменял пока я читал курсор. Кстати, serializable level мне гарантирует, что если я повторно открою тот же курсор, то опять прочитаю то же самое. Я же работаю как бы со своей личной копией данных и другие юзеры мне как бы безразличны в данном случае. Поэтому все разговоры об isolation levels, locks тут как бы побоку.