Visibilty of statements in a view related to transaction?
i have a question about data visibility in database views. I use Oracle. I have a table myscheme.users and a table global.users (myscheme and global are schemes) There is a view "globalusers" in the global scheme which merges both user tables.
I have a java application that inserts a new user in the myscheme.users table. After that it makes a query for the new user in the globalusers-view to load the newly created user. As all this happens inside the same transaction. My question is now, why is the newly created user visible in the myscheme.user table but not in the globalusers-view? When the transaction has been closed, the user is also in the globaluser-view.
My application is running inside开发者_如何学Go a JBoss 5 using hibernate.
The view is defined in that way:
CREATE OR REPLACE VIEW V_USERACCOUNT AS
SELECT u.USERACCOUNT_ID,u.USERNAME,u.INITIALS,u.COMMONNAME,u.PASSWORD,'local' AS source
FROM "USERACCOUNT" u
UNION
SELECT u1.USERACCOUNT_ID,u1.USERNAME,u1.INITIALS,u1.COMMONNAME,u1.PASSWORD 'global' AS source
FROM GLOBAL."USERACCOUNT" u1
If the INSERT
into the table and the SELECT
from the view are, in fact, part of the same database transaction (which necessarily implies that they are executed in the same database session), the newly inserted row would be visible in the view (assuming the row meets whatever criteria the view uses to determine which rows to display). If the new user is not in the view, that implies that either
- The
INSERT
and theSELECT
are not part of the same database transaction, or - The
INSERT
is not sufficient to cause the row to be available in the view. Perhaps there is some other lookup table that is joined in the view that doesn't have the data for the new row when you query the view but the lookup is populated later in the transaction. - You have a bug somewhere in your code where the
INSERT
isn't actually happening when you think it is or the query isn't happening after theINSERT
or the query isn't actually hitting the view you posted.
Based on your latest update, it appears that #1 is almost certainly the problem. If you have different DataSources, the INSERT
uses one DataSource, and the SELECT
uses a second DataSource, the two operations are not happening in the same database transaction. They may be happening in the same application server transaction-- the application server may well be creating a distributed transaction-- but if it's not the same database transaction, you won't be able to see uncommitted changes.
精彩评论