473,224 Members | 1,464 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

Question about commit

I have a question about oracle commit and transactions. Following scenario:

Process A performs a single sql-INSERT into a table and commits the
transaction. Then he informs process B (ipc) to read the new date. So
process B starts "select ..." but does not get the previously inserted
row. The timespan between commit and select is very short.
(NOTE: two different sessions are used)

Questions:
1.) Does commit when returning from call ensure, that all changes are
immediatelly visible to all other Sessions/transactions?
2.) Does commit ensure only that all data is stored persistent, but
changes are deferred visible to other transactions?
3.) May the "select ..." cause the problem? Other than dml statements a
select does not start a transaction. Would "select for update" instead
solve the problem?

regards markus
Jul 19 '05 #1
11 12623

"Markus Breuer" <ma***********@gmx.de> wrote in message
news:ci**********@pentheus.materna.de...
| I have a question about oracle commit and transactions. Following
scenario:
|
| Process A performs a single sql-INSERT into a table and commits the
| transaction. Then he informs process B (ipc) to read the new date. So
| process B starts "select ..." but does not get the previously inserted
| row. The timespan between commit and select is very short.
| (NOTE: two different sessions are used)
|
| Questions:
| 1.) Does commit when returning from call ensure, that all changes are
| immediatelly visible to all other Sessions/transactions?
| 2.) Does commit ensure only that all data is stored persistent, but
| changes are deferred visible to other transactions?
| 3.) May the "select ..." cause the problem? Other than dml statements a
| select does not start a transaction. Would "select for update" instead
| solve the problem?
|
| regards markus

the commit completes the transaction before returning control to the issuing
application, and the data is immediately available to all other users with
privileges

is the second process selecting from a view?
do you have VPD policies?
some more details about the processes and SQL involved would help. likely
the version would also be helpful

++ mcs
Jul 19 '05 #2
Mark C. Stock wrote:
"Markus Breuer" <ma***********@gmx.de> wrote in message
news:ci**********@pentheus.materna.de...
| I have a question about oracle commit and transactions. Following
scenario:
|
| Process A performs a single sql-INSERT into a table and commits the
| transaction. Then he informs process B (ipc) to read the new date. So
| process B starts "select ..." but does not get the previously inserted
| row. The timespan between commit and select is very short.
| (NOTE: two different sessions are used)
|
| Questions:
| 1.) Does commit when returning from call ensure, that all changes are
| immediatelly visible to all other Sessions/transactions?
| 2.) Does commit ensure only that all data is stored persistent, but
| changes are deferred visible to other transactions?
| 3.) May the "select ..." cause the problem? Other than dml statements a
| select does not start a transaction. Would "select for update" instead
| solve the problem?
|
| regards markus

the commit completes the transaction before returning control to the issuing
application, and the data is immediately available to all other users with
privileges

is the second process selecting from a view?
Is there any difference between selecting from a table and form a view?
Our processes directly access the table.
do you have VPD policies?
I dont know what VPD means...
some more details about the processes and SQL involved would help. likely
the version would also be helpful


We use a Oracle in the Versions 8.1.7.4 and 9.2.0.5. Both show the same
problem. The Application is written in c++ using the oracle oci. The
described processes A and B are threads within the same process. But
that should not make a difference.

The main Question is: Does oracle ensure that a returned commit makes
changes immediatelly available to all other sessions. And: could there
be an restriction when using simple select statements? => select does
not start its own transaction.

regards markus
Jul 19 '05 #3
Markus Breuer <ma***********@gmx.de> wrote in
news:ci**********@pentheus.materna.de:
I have a question about oracle commit and transactions. Following
scenario:

Process A performs a single sql-INSERT into a table and commits the
transaction. Then he informs process B (ipc) to read the new date.
As strange as this may sound PRIOR to issuing the SELECT,
Process B needs to issue a COMMIT.

I suspect that this will allow Process B to "see" the new data.
So
process B starts "select ..." but does not get the previously inserted
row. The timespan between commit and select is very short.
(NOTE: two different sessions are used)

Questions:
1.) Does commit when returning from call ensure, that all changes are
immediatelly visible to all other Sessions/transactions?
2.) Does commit ensure only that all data is stored persistent, but
changes are deferred visible to other transactions?
3.) May the "select ..." cause the problem? Other than dml statements
a select does not start a transaction. Would "select for update"
instead solve the problem?

regards markus


Jul 19 '05 #4

"Markus Breuer" <ma***********@gmx.de> wrote in message
news:ci**********@pentheus.materna.de...
| Mark C. Stock wrote:
|
| > "Markus Breuer" <ma***********@gmx.de> wrote in message
| > news:ci**********@pentheus.materna.de...
| > | I have a question about oracle commit and transactions. Following
| > scenario:
| > |
| > | Process A performs a single sql-INSERT into a table and commits the
| > | transaction. Then he informs process B (ipc) to read the new date. So
| > | process B starts "select ..." but does not get the previously inserted
| > | row. The timespan between commit and select is very short.
| > | (NOTE: two different sessions are used)
| > |
| > | Questions:
| > | 1.) Does commit when returning from call ensure, that all changes are
| > | immediatelly visible to all other Sessions/transactions?
| > | 2.) Does commit ensure only that all data is stored persistent, but
| > | changes are deferred visible to other transactions?
| > | 3.) May the "select ..." cause the problem? Other than dml statements
a
| > | select does not start a transaction. Would "select for update" instead
| > | solve the problem?
| > |
| > | regards markus
| >
| > the commit completes the transaction before returning control to the
issuing
| > application, and the data is immediately available to all other users
with
| > privileges
| >
| > is the second process selecting from a view?
|
| Is there any difference between selecting from a table and form a view?
| Our processes directly access the table.
|
| > do you have VPD policies?
|
| I dont know what VPD means...
|
| > some more details about the processes and SQL involved would help.
likely
| > the version would also be helpful
|
| We use a Oracle in the Versions 8.1.7.4 and 9.2.0.5. Both show the same
| problem. The Application is written in c++ using the oracle oci. The
| described processes A and B are threads within the same process. But
| that should not make a difference.
|
| The main Question is: Does oracle ensure that a returned commit makes
| changes immediatelly available to all other sessions. And: could there
| be an restriction when using simple select statements? => select does
| not start its own transaction.
|
| regards markus
|
|

If you're selecting from a view, the view may have a predicate that filters
out the new row.

VPD: Virtual Private Database -- VPD policies automatically add predicates
to SELECT statements issued against tables or view, which could also filter
out the row.

Threads within the same process should not make a difference in and of
itself -- but does the 'B' thread have any SET TRANSACTION READ ONLY
statements? That would set transaction-level read consistency so that you
would not see the newly committed data.

++ mcs
Jul 19 '05 #5

"Ana C. Dent" <an*******@hotmail.com> wrote in message
news:Xn*********************@68.6.19.6...
| Markus Breuer <ma***********@gmx.de> wrote in
| news:ci**********@pentheus.materna.de:
|
| > I have a question about oracle commit and transactions. Following
| > scenario:
| >
| > Process A performs a single sql-INSERT into a table and commits the
| > transaction. Then he informs process B (ipc) to read the new date.
|
| As strange as this may sound PRIOR to issuing the SELECT,
| Process B needs to issue a COMMIT.
|

That does sound strange... the only reason for this would be if B is in a
read-only transaction... (see my other post).

Issuing a COMMIT to see other user's changes is never a requirement.

If B is in a read-only transaction, then a COMMIT or ROLLBACK should only be
entered when the read-only transaction is completed (per the business
functionality specification), not as a work around to a scenario that is not
yet fully analyzed.

++ mcs
Jul 19 '05 #6
Markus Breuer wrote:
I have a question about oracle commit and transactions. Following
scenario:

Process A performs a single sql-INSERT into a table and commits the
transaction. Then he informs process B (ipc) to read the new date. So
process B starts "select ..." but does not get the previously inserted
row. The timespan between commit and select is very short.
(NOTE: two different sessions are used)

Questions:
1.) Does commit when returning from call ensure, that all changes are
immediatelly visible to all other Sessions/transactions?
2.) Does commit ensure only that all data is stored persistent, but
changes are deferred visible to other transactions?
3.) May the "select ..." cause the problem? Other than dml statements a
select does not start a transaction. Would "select for update" instead
solve the problem?

regards markus


Instead of trying to fix your solution, you might be able to use other
Oracle capabilities to fix your problem ...

If you are attempting to communicate between session, you might want to look
at "autonomous transactions" to do the interaction using tables or
"dbms_pipe" for interaction using IPCs.
Jul 19 '05 #7
Mark C. Stock wrote:
That does sound strange... the only reason for this would be if B is in a
read-only transaction... (see my other post).


I think you're right. If read-only tranactions are the only reason to
hide changes to the other session, my error must be located within the
application.

Thx for your answer!

regards markus
Jul 19 '05 #8
> Threads within the same process should not make a difference in and of
itself -- but does the 'B' thread have any SET TRANSACTION READ ONLY
statements? That would set transaction-level read consistency so that you
would not see the newly committed data.


No, read consistency was net set. But in general, could this problem
happen? I think of database and session default, no changes appear to
the current session...

regards markus
Jul 19 '05 #9
"Mark C. Stock" <mcstockX@Xenquery .com> wrote in
news:4P********************@comcast.com:

"Ana C. Dent" <an*******@hotmail.com> wrote in message
news:Xn*********************@68.6.19.6...
| Markus Breuer <ma***********@gmx.de> wrote in
| news:ci**********@pentheus.materna.de:
|
| > I have a question about oracle commit and transactions. Following
| > scenario:
| >
| > Process A performs a single sql-INSERT into a table and commits the
| > transaction. Then he informs process B (ipc) to read the new date.
|
| As strange as this may sound PRIOR to issuing the SELECT,
| Process B needs to issue a COMMIT.
|

That does sound strange... the only reason for this would be if B is
in a read-only transaction... (see my other post).

Issuing a COMMIT to see other user's changes is never a requirement.
Never, say "never". ;-)

If B is in a read-only transaction, then a COMMIT or ROLLBACK should
only be entered when the read-only transaction is completed (per the
business functionality specification), not as a work around to a
scenario that is not yet fully analyzed.

++ mcs


Oracle is too brain dead to know about "read-only" transactions.
Oracle GUARENTEES a read consistant view of the database.
If Process B has issued a SELECT prior to Process A doing the COMMIT,
then Oracle ENSURES Process B won't see the changed data. This is
because Oracle can't know what Process B intends to do with the
data from the 1st SELECT. The only way I know how to convince Oracle
that my process wants to see "new data" is to issue a COMMIT (or
ROLLBACK) to indicate all my previous activity is a completed
transaction.After my session issues a COMMIT, Oracle will present
to my session data as it exists at the time of my next SELECT!

You are free to disagree & provide proof to the contrary.
Jul 19 '05 #10
Ana C. Dent wrote:

Oracle is too brain dead to know about "read-only" transactions.
Oracle GUARENTEES a read consistant view of the database.
If Process B has issued a SELECT prior to Process A doing the COMMIT,
then Oracle ENSURES Process B won't see the changed data. This is
because Oracle can't know what Process B intends to do with the
data from the 1st SELECT. The only way I know how to convince Oracle
that my process wants to see "new data" is to issue a COMMIT (or
ROLLBACK) to indicate all my previous activity is a completed
transaction.After my session issues a COMMIT, Oracle will present
to my session data as it exists at the time of my next SELECT!


There are several scenarios & purposes possible.

If the purpose is to communicate intermediate results to other processes,
autonomous transactions might be useful. They can do their own commits
without affecting the transaction's committment state.

Jul 19 '05 #11

"Ana C. Dent" <an*******@hotmail.com> wrote in message
news:Xn*********************@68.6.19.6...
| "Mark C. Stock" <mcstockX@Xenquery .com> wrote in
| news:4P********************@comcast.com:
|
| >
| > "Ana C. Dent" <an*******@hotmail.com> wrote in message
| > news:Xn*********************@68.6.19.6...
| >| Markus Breuer <ma***********@gmx.de> wrote in
| >| news:ci**********@pentheus.materna.de:
| >|
| >| > I have a question about oracle commit and transactions. Following
| >| > scenario:
| >| >
| >| > Process A performs a single sql-INSERT into a table and commits the
| >| > transaction. Then he informs process B (ipc) to read the new date.
| >|
| >| As strange as this may sound PRIOR to issuing the SELECT,
| >| Process B needs to issue a COMMIT.
| >|
| >
| > That does sound strange... the only reason for this would be if B is
| > in a read-only transaction... (see my other post).
| >
| > Issuing a COMMIT to see other user's changes is never a requirement.
|
| Never, say "never". ;-)
|

With default Oracle behavior, issuing a COMMIT to for another user's changes
to be seen by a new SELECT is never, never a requirement -- issuing a COMMIT
or ROLLBACK to end a read-only transaction is a requirement (as noted in my
last post, just below), or to start a new serialized transaction if the
session's isolation level has been set to to SERIALIZABLE.

My point is do not (never) throw in a COMMIT based on a wrong assumption,
i.e., that other user's COMMITs are insufficient to make their changed data
permanent (the main quesion of the OP) -- find out why normal default
behavior is not working in your application.
| >
| > If B is in a read-only transaction, then a COMMIT or ROLLBACK should
| > only be entered when the read-only transaction is completed (per the
| > business functionality specification), not as a work around to a
| > scenario that is not yet fully analyzed.
| >
| > ++ mcs
| >
| >
|
| Oracle is too brain dead to know about "read-only" transactions.
| Oracle GUARENTEES a read consistant view of the database.
| If Process B has issued a SELECT prior to Process A doing the COMMIT,
| then Oracle ENSURES Process B won't see the changed data. This is
| because Oracle can't know what Process B intends to do with the
| data from the 1st SELECT. The only way I know how to convince Oracle
| that my process wants to see "new data" is to issue a COMMIT (or
| ROLLBACK) to indicate all my previous activity is a completed
| transaction.After my session issues a COMMIT, Oracle will present
| to my session data as it exists at the time of my next SELECT!
|
| You are free to disagree & provide proof to the contrary.
|
|

This is really a matter of understanding Oracle's read-consistency model
(and transaction isolation levels). By default oracle operates with
statement level read-consistency, but it can be set to session level.

If you find that you need to issue a commit to see data committed by other
users, then something in your environment is establising session-level
read-consistency (ie, SET TRANSACTION READ ONLY) or your session is set to
SERIALIZABLE.

As for as statement-level read consistency, Oracle has worked that way at
least since the beginning (at least v4, don't remember when SET TRANSACTION
and isolation levels where implemented).

This is thoroughly covered in the docs and is really Oracle 101. From page
10-7 of the 7.3.2 manual:
----------------------------------------------
Transaction Level Read Consistency
Oracle also allows the option of enforcing transaction-level read
consistency. When a transaction executes in serializable mode (see below),
all data accesses reflect the state of the database as of the time the
transaction began. This means that the data seen by all queries within the
same transaction is consistent with respect to a single point in time,
except that queries made by a serializable transaction do see changes made
by the transaction itself. Therefore, transaction-level read consistency
produces repeatable reads and does not expose a query to phantoms.

Oracle Isolation Levels

Oracle provides three transaction isolation modes:

Read committed.
This is the default transaction isolation level. Each query executed by a
transaction sees only data that was committed before the query (not the
transaction) began. An Oracle query will never read dirty (uncommitted)
data.
Because Oracle does not prevent other transactions from modifying the data
read by a query, that data may be changed by other transactions between two
executions of the query. Thus, a transaction that executes a given query
twice may experience both non-repeatable read and phantoms.

Serializable transactions
Serializable transactions see only those changes that were committed at the
time the transaction began, plus those changes made by the transaction
itself through INSERT, UPDATE, and DELETE statements. Serializable
transactions do not see non-repeatable reads or phantoms.

Read only
Read only transactions see only those changes that were committed at the
time the transaction began and do not allow INSERT, UPDATE, and DELETE
statements.
----------------------------------------------

Anybody have any other comments/clarification on read-consistency and
isolations levels?

++ mcs
Jul 19 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: JP Belanger | last post by:
I have a question on select count(), which may betray my lack of database knowledge. Here goes. I have java code driving a transaction which goes like this: select count(*) from table ...
8
by: nephish | last post by:
Hey there, sorry , second question in as many days. i am trying to do some stuff with MySQLdb and the thing is, i can select records and such, but when i try to delete them, they dont really go...
2
by: Stéphane Cazeaux | last post by:
Hi I currently use pgsql 7.2.4 (but the following has also been seen on pgsql 7.3.3) with a transaction level set to "read committed". It do a lot of little tests to understand how concurrency...
8
by: Thomasb | last post by:
With a background in MS SQL Server programming I'm used to temporary tables. Have just started to work with DB2 ver 7 on z/OS and stumbled into the concept of GLOBAL TEMPORARY TABLE. I have...
11
by: Mark Yudkin | last post by:
The documentation is unclear (at least to me) on the permissibility of accessing DB2 (8.1.5) concurrently on and from Windows 2000 / XP / 2003, with separate transactions scope, from separate...
11
by: Madison Kelly | last post by:
Hi all, I am new to the list and I didn't want to seem rude at all so I wanted to ask if this was okay first. I have a program I have written in perl which uses a postgresSQL database as the...
8
by: Gregor Kovaè | last post by:
Hi! If I create a procedure with BEGIN ATOMIC block I know these means that when an error occurs all the SQL statements will be rolled back. The question I have is: If I select all records form...
9
by: David Eades | last post by:
Hi all Complete newbie here, so apologies if this is the wrong forum. I've been asked to use mysql and asp to make a simple bidding system (rather like a simple ebay), whereby users can use a...
11
by: Markus Breuer | last post by:
I have a question about oracle commit and transactions. Following scenario: Process A performs a single sql-INSERT into a table and commits the transaction. Then he informs process B (ipc) to...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.