473,903 Members | 3,244 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 12739

"Markus Breuer" <ma***********@ gmx.de> wrote in message
news:ci******** **@pentheus.mat erna.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.mat erna.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.mat erna.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.mat erna.de...
| Mark C. Stock wrote:
|
| > "Markus Breuer" <ma***********@ gmx.de> wrote in message
| > news:ci******** **@pentheus.mat erna.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*******@hotm ail.com> wrote in message
news:Xn******** *************@6 8.6.19.6...
| Markus Breuer <ma***********@ gmx.de> wrote in
| news:ci******** **@pentheus.mat erna.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@Xenqu ery .com> wrote in
news:4P******** ************@co mcast.com:

"Ana C. Dent" <an*******@hotm ail.com> wrote in message
news:Xn******** *************@6 8.6.19.6...
| Markus Breuer <ma***********@ gmx.de> wrote in
| news:ci******** **@pentheus.mat erna.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.Aft er 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

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

Similar topics

7
10885
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 (Java code in the same transaction) if count < 50 then ...
8
2029
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 away. like this >>> cursor.execute("DELETE FROM table WHERE autoinc > 1000") 240L
2
1977
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 control works. Let see this scenario: We have a table named "test_count" and a field named "count" The table contains 1 entry with count=1
8
7952
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 created a temporary database with a tables space. Verified that DECLARE GLOBAL TEMPORARY TABLE TEMP (A INTEGER); INSERT INTO SESSION.TEMP VALUES(10); SELECT A FROM SESSION.TEMP; works from a query tool.
11
4278
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 threads of a multithreaded program using embedded SQL. Since the threads do not need to share transaction scopes, the sqleAttachToCtx family of APIs do not seem to be necessary. <quote> In the default implementation of threaded applications against...
11
2298
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 backend. The program works but the performance is really bad. I have been reading as much as I can on optimizing performance but still it isn't very reasonable. At one point I had my program able to process 175,000 records in 16min 10sec on a...
8
1394
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 a table and process them. The processing of those records takes about 1 minute for example. In the meanwhile new records come into the same table. If I issues the same SQL statement will I see these new rows too ?
9
3229
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 web browser to view a highest bid and can make a bid. My question is; how can I be sure that when a user submits a bid, that another user isn't also currently submittimg a bid, i.e i can tell user A
11
403
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 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...
0
9999
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11282
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10873
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10983
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9684
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7206
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6088
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4726
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4308
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.