what is the default value for IBM Informix set isolation level
1 the stored procedure
create procedure sp_count_demo(
i_user_id varchar(30)
)
returning p_count as num_of_row ;
define p_count integer ;
set isolation to dirty read ;
let p_row = 0 ;
select count(*)
into p_count
from some_table a
where a.user_id = i_user_id
;
return p_row;
end procedure ;
2 The procedure at (1) will be called from java webapps with connection pool
3 Do I need to set the isolation level back to previous value before returning the result? (ie to avoid another process reusing the connection from having "dirty read" isolation level)
4 What is the default isolation level
5 Where/How can I get the default value for isolation level
Thanks in advance
connection informix pool isolation
asked Nov 19 '18 at 9:22
user3737906user3737906
2715
add a comment |
1 the stored procedure
create procedure sp_count_demo(
i_user_id varchar(30)
)
returning p_count as num_of_row ;
define p_count integer ;
set isolation to dirty read ;
let p_row = 0 ;
select count(*)
into p_count
from some_table a
where a.user_id = i_user_id
;
return p_row;
end procedure ;
2 The procedure at (1) will be called from java webapps with connection pool
3 Do I need to set the isolation level back to previous value before returning the result? (ie to avoid another process reusing the connection from having "dirty read" isolation level)
4 What is the default isolation level
5 Where/How can I get the default value for isolation level
Thanks in advance
connection informix pool isolation
asked Nov 19 '18 at 9:22
user3737906user3737906
2715
add a comment |
1 the stored procedure
create procedure sp_count_demo(
i_user_id varchar(30)
)
returning p_count as num_of_row ;
define p_count integer ;
set isolation to dirty read ;
let p_row = 0 ;
select count(*)
into p_count
from some_table a
where a.user_id = i_user_id
;
return p_row;
end procedure ;
2 The procedure at (1) will be called from java webapps with connection pool
3 Do I need to set the isolation level back to previous value before returning the result? (ie to avoid another process reusing the connection from having "dirty read" isolation level)
4 What is the default isolation level
5 Where/How can I get the default value for isolation level
Thanks in advance
connection informix pool isolation
asked Nov 19 '18 at 9:22
user3737906user3737906
2715
1 the stored procedure
create procedure sp_count_demo(
i_user_id varchar(30)
)
returning p_count as num_of_row ;
define p_count integer ;
set isolation to dirty read ;
let p_row = 0 ;
select count(*)
into p_count
from some_table a
where a.user_id = i_user_id
;
return p_row;
end procedure ;
2 The procedure at (1) will be called from java webapps with connection pool
3 Do I need to set the isolation level back to previous value before returning the result? (ie to avoid another process reusing the connection from having "dirty read" isolation level)
4 What is the default isolation level
5 Where/How can I get the default value for isolation level
Thanks in advance
connection informix pool isolation
connection informix pool isolation
asked Nov 19 '18 at 9:22
user3737906user3737906
2715
asked Nov 19 '18 at 9:22
user3737906user3737906
2715
asked Nov 19 '18 at 9:22
user3737906user3737906
2715
asked Nov 19 '18 at 9:22
user3737906user3737906
2715
asked Nov 19 '18 at 9:22
user3737906user3737906
2715
2715
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Since a connection pool is in use the stored procedure should return the isolation level to its previous setting in order to avoid unexpected results when another app uses the same connection. The default isolation level depends on the logging mode of the database:
- For an unlogged database it will effectively be "Dirty Read" (shown as NL by the onstat -g ses command).
- For a mode ANSI database it will be "Repeatable Read."
- For other logged databases it will be "Committed Read."
The onconfig parameter USELASTCOMMITTED can also be used to change how the default isolation level is used. More information on that can be found in the Knowledge Center (search on USELASTCOMMITTED).
It is possible for a session to find out its current isolation level using a query against the sysmaster database. This query was run on Informix 12.10 but should also be valid for 11.70:
select tx.isolevel
from sysmaster:systxptab tx, sysmaster:sysrstcb r, sysmaster:sysscblst s
where s.address = r.scb and tx.owner = r.address
and s.sid = dbinfo("sessionid");
It returns the isolation level as an integer which is an internal value - for example committed read has value 2. I don't believe the mapping of isolation level to integer value is published so you will need to experiment with setting different levels for a session and then running the above query.
answered Nov 20 '18 at 10:06
Simon RiddleSimon Riddle
37111
1
Another way to verify and set the isolation level could be using the JDBCgetTransactionIsolation()andsetTransactionIsolation(int level)methods. It needs to be tested of course, to check if behaves as expected.
– Luís Marques
Nov 20 '18 at 12:07
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53371566%2fwhat-is-the-default-value-for-ibm-informix-set-isolation-level%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Since a connection pool is in use the stored procedure should return the isolation level to its previous setting in order to avoid unexpected results when another app uses the same connection. The default isolation level depends on the logging mode of the database:
- For an unlogged database it will effectively be "Dirty Read" (shown as NL by the onstat -g ses command).
- For a mode ANSI database it will be "Repeatable Read."
- For other logged databases it will be "Committed Read."
The onconfig parameter USELASTCOMMITTED can also be used to change how the default isolation level is used. More information on that can be found in the Knowledge Center (search on USELASTCOMMITTED).
It is possible for a session to find out its current isolation level using a query against the sysmaster database. This query was run on Informix 12.10 but should also be valid for 11.70:
select tx.isolevel
from sysmaster:systxptab tx, sysmaster:sysrstcb r, sysmaster:sysscblst s
where s.address = r.scb and tx.owner = r.address
and s.sid = dbinfo("sessionid");
It returns the isolation level as an integer which is an internal value - for example committed read has value 2. I don't believe the mapping of isolation level to integer value is published so you will need to experiment with setting different levels for a session and then running the above query.
answered Nov 20 '18 at 10:06
Simon RiddleSimon Riddle
37111
1
Another way to verify and set the isolation level could be using the JDBCgetTransactionIsolation()andsetTransactionIsolation(int level)methods. It needs to be tested of course, to check if behaves as expected.
– Luís Marques
Nov 20 '18 at 12:07
add a comment |
Since a connection pool is in use the stored procedure should return the isolation level to its previous setting in order to avoid unexpected results when another app uses the same connection. The default isolation level depends on the logging mode of the database:
- For an unlogged database it will effectively be "Dirty Read" (shown as NL by the onstat -g ses command).
- For a mode ANSI database it will be "Repeatable Read."
- For other logged databases it will be "Committed Read."
The onconfig parameter USELASTCOMMITTED can also be used to change how the default isolation level is used. More information on that can be found in the Knowledge Center (search on USELASTCOMMITTED).
It is possible for a session to find out its current isolation level using a query against the sysmaster database. This query was run on Informix 12.10 but should also be valid for 11.70:
select tx.isolevel
from sysmaster:systxptab tx, sysmaster:sysrstcb r, sysmaster:sysscblst s
where s.address = r.scb and tx.owner = r.address
and s.sid = dbinfo("sessionid");
It returns the isolation level as an integer which is an internal value - for example committed read has value 2. I don't believe the mapping of isolation level to integer value is published so you will need to experiment with setting different levels for a session and then running the above query.
answered Nov 20 '18 at 10:06
Simon RiddleSimon Riddle
37111
1
Another way to verify and set the isolation level could be using the JDBCgetTransactionIsolation()andsetTransactionIsolation(int level)methods. It needs to be tested of course, to check if behaves as expected.
– Luís Marques
Nov 20 '18 at 12:07
add a comment |
Since a connection pool is in use the stored procedure should return the isolation level to its previous setting in order to avoid unexpected results when another app uses the same connection. The default isolation level depends on the logging mode of the database:
- For an unlogged database it will effectively be "Dirty Read" (shown as NL by the onstat -g ses command).
- For a mode ANSI database it will be "Repeatable Read."
- For other logged databases it will be "Committed Read."
The onconfig parameter USELASTCOMMITTED can also be used to change how the default isolation level is used. More information on that can be found in the Knowledge Center (search on USELASTCOMMITTED).
It is possible for a session to find out its current isolation level using a query against the sysmaster database. This query was run on Informix 12.10 but should also be valid for 11.70:
select tx.isolevel
from sysmaster:systxptab tx, sysmaster:sysrstcb r, sysmaster:sysscblst s
where s.address = r.scb and tx.owner = r.address
and s.sid = dbinfo("sessionid");
It returns the isolation level as an integer which is an internal value - for example committed read has value 2. I don't believe the mapping of isolation level to integer value is published so you will need to experiment with setting different levels for a session and then running the above query.
answered Nov 20 '18 at 10:06
Simon RiddleSimon Riddle
37111
Since a connection pool is in use the stored procedure should return the isolation level to its previous setting in order to avoid unexpected results when another app uses the same connection. The default isolation level depends on the logging mode of the database:
- For an unlogged database it will effectively be "Dirty Read" (shown as NL by the onstat -g ses command).
- For a mode ANSI database it will be "Repeatable Read."
- For other logged databases it will be "Committed Read."
The onconfig parameter USELASTCOMMITTED can also be used to change how the default isolation level is used. More information on that can be found in the Knowledge Center (search on USELASTCOMMITTED).
It is possible for a session to find out its current isolation level using a query against the sysmaster database. This query was run on Informix 12.10 but should also be valid for 11.70:
select tx.isolevel
from sysmaster:systxptab tx, sysmaster:sysrstcb r, sysmaster:sysscblst s
where s.address = r.scb and tx.owner = r.address
and s.sid = dbinfo("sessionid");
It returns the isolation level as an integer which is an internal value - for example committed read has value 2. I don't believe the mapping of isolation level to integer value is published so you will need to experiment with setting different levels for a session and then running the above query.
answered Nov 20 '18 at 10:06
Simon RiddleSimon Riddle
37111
answered Nov 20 '18 at 10:06
Simon RiddleSimon Riddle
37111
answered Nov 20 '18 at 10:06
Simon RiddleSimon Riddle
37111
answered Nov 20 '18 at 10:06
Simon RiddleSimon Riddle
37111
37111
1
Another way to verify and set the isolation level could be using the JDBCgetTransactionIsolation()andsetTransactionIsolation(int level)methods. It needs to be tested of course, to check if behaves as expected.
– Luís Marques
Nov 20 '18 at 12:07
add a comment |
1
Another way to verify and set the isolation level could be using the JDBCgetTransactionIsolation()andsetTransactionIsolation(int level)methods. It needs to be tested of course, to check if behaves as expected.
– Luís Marques
Nov 20 '18 at 12:07
1
1
Another way to verify and set the isolation level could be using the JDBC
getTransactionIsolation() and setTransactionIsolation(int level) methods. It needs to be tested of course, to check if behaves as expected.– Luís Marques
Nov 20 '18 at 12:07
Another way to verify and set the isolation level could be using the JDBC
getTransactionIsolation() and setTransactionIsolation(int level) methods. It needs to be tested of course, to check if behaves as expected.– Luís Marques
Nov 20 '18 at 12:07
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53371566%2fwhat-is-the-default-value-for-ibm-informix-set-isolation-level%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
