如何在DB2上获取当前的隔离级别？ [英] How to obtain current isolation level on DB2?

问题描述

  SAVEPOINT STOP_HERE我试图了解事务如何在数据库中工作，并做到这一点我写了以下测试SQL：旋转保持电路; 
 
 INSERT INTO TESTSCHEMA。test（ID，NAME）VALUES（89898，'SDFASDFASD'）; 
 
 ROLLBACK TO SAVEPOINT STOP_HERE; 
 
 SELECT * FROM TESTSCHEMA。test; 
  

执行此代码后，将一行添加到表中。但是如果我在开始添加以下行：

  SET TRANSACTION ISOLATION LEVEL READ Commit Committed; 
  

所有工作都符合我的预期，即事务正确回滚，DB中没有新条目，但如果我再次运行此代码，Data Studio会在第一行显示错误：

  [SQL0428]无法启动SQL语句。 
  

所以我的问题是：
有没有办法获得当前的隔离级别，为什么我不能设置隔离级别超过1次？

我将非常感谢所有答案和链接。

PS。我正在使用DB2 / iSeries V5R4。

PPS。对不起，我的坏英文

解决方案

你可以从特殊寄存器获得当前的隔离级别当前隔离。你得到 SQL0428 ，因为在设置隔离级别之前没有进行COMMIT或ROLLBACK，并且该会话中仍有进行中的事务。

I am trying to learn how transactions work in DB and to do this I wrote the following test SQL:

SAVEPOINT STOP_HERE ON ROLLBACK RETAIN CURSORS;

INSERT INTO TESTSCHEMA."test" (ID, NAME) VALUES (89898, 'SDFASDFASD');

ROLLBACK TO SAVEPOINT STOP_HERE;

SELECT * FROM TESTSCHEMA."test";

After execution of this code the one row is added into the table. But if I add the following line at the beginning:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

all work as I expected, i.e. transaction correctly rolled back and no new entries in DB, but if I run this code again the Data Studio shows me error in the first line:

[SQL0428] SQL statement can not be launched.

So my questions are: Is there a way to obtain current isolation level and why I can't set isolation level more than 1 time?

I would be very thankful for all answers and links.

PS. I am using DB2/iSeries V5R4.

PPS. Sorry for my bad English

解决方案

You can probably get the current isolation level from special register current isolation. You get the SQL0428 because you didn't COMMIT or ROLLBACK before setting the isolation level, and there were transactions still in process in that session.

这篇关于如何在DB2上获取当前的隔离级别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！