Stale data and outdated @version values are being returned after performing an Edge update operation in OrientDB version 3.2.34 and later.

[arpitdd123]

Hello,

I am performing an update operation on an edge and then attempting to retrieve the updated value. However, after executing the SELECT query, the OrientDB API returns stale data for the edge. The returned record does not reflect the updated attribute value, and the @Version is also outdated.

When I check the same record in OrientDB Studio, the updated values and the correct version are displayed.

Below are the steps to reproduce.

Step 1 : Schema setup

CREATE CLASS VertexA EXTENDS V;
CREATE PROPERTY VertexA.StringTest STRING;
CREATE PROPERTY VertexA.IntTest INTEGER;
 
CREATE CLASS EdgeB EXTENDS E;
CREATE PROPERTY EdgeB.StringEdgeTest STRING;
CREATE PROPERTY EdgeB.IntegerEdgeTest INTEGER;
 
INSERT INTO VertexA SET StringTest = 'A', IntTest = 25;
INSERT INTO VertexA SET StringTest = 'B', IntTest = 30;

Step 2 : Create database connection using OPartitionedDatabasePool-

String databaseUrl = "url";
String userName = "username";
String password = "password";
OPartitionedDatabasePool pool = new OPartitionedDatabasePool(databaseUrl, userName, password);
ODatabaseDocument db = pool.acquire();

Step 3 : Create Edge from A to B:

db.activateOnCurrentThread();
db.command("CREATE EDGE EdgeB FROM "
    + "(SELECT FROM VertexA WHERE StringTest = 'A') TO "
    + "(SELECT FROM VertexA WHERE StringTest = 'B') "
    + "SET StringTest = 'A-B', IntTest = 3");
db.commit();

Step 4 : Update Edge A to B:

db.activateOnCurrentThread();
db.command("UPDATE EdgeB SET IntTest = 30 WHERE StringTest = 'A-B'");
db.commit();

Step 5 : Fetch Updated Edge:

db.activateOnCurrentThread();
OResultSet resultSet = db.command("SELECT StringTest, IntTest FROM EdgeB WHERE IntTest = 30");

Why is the Java API returning stale document data after executing the query while OrientDB Studio shows the correct updated version?
What is the recommended way to ensure that the latest version of the document is retrieved?

Note: I have also filed issue for same : #10726

Thanks

[newDmoon]

Technical Explanation: The L1 Cache Issue
The behavior you are experiencing is a classic case of Session-level Caching (L1 Cache) in OrientDB. When you use ODatabaseDocument (via OPartitionedDatabasePool), the driver keeps local copies of objects in memory to optimize performance.

However, when you execute an SQL UPDATE via db.command(), the local cache in your current session is not always automatically synchronized with the changes made on the server side.

1. Why does this happen?
   OrientDB utilizes a Local Transaction Cache. When a record is loaded or created, it is stored in the cache of that specific db instance.

The Conflict: When you run db.command("UPDATE..."), it is processed as a server-side SQL command.

The Result: When you subsequently run a SELECT, the API sees that a record with that specific ORID already exists in your local memory. Instead of fetching the fresh data from the disk/server, it returns the stale version from the cache, which still has the old @Version.

Studio vs. API: OrientDB Studio shows the correct data because every query in Studio typically runs in a fresh, isolated session, avoiding the stale cache of your long-running Java session.

2. Recommended Solutions
   Option A: Clear the Local Cache (Simplest)
   Force the current session to drop all cached objects before performing the SELECT. This ensures the next query hits the physical storage.

Java
db.activateOnCurrentThread();
db.getLocalCache().clear(); // Clears the Level 1 cache
OResultSet resultSet = db.command("SELECT FROM EdgeB WHERE IntTest = 30");
Option B: Use the NOCACHE Keyword
You can instruct OrientDB to bypass the cache specifically for your query:

SQL
SELECT FROM EdgeB WHERE IntTest = 30 NOCACHE
Option C: Manual Reload
If you are holding an instance of an OEdge or ODocument object, you can manually synchronize it with the database:

Java
edge.reload(); // Re-fetches the latest attributes and @Version
Option D: Refresh the Connection
If Step 4 and Step 5 are distinct logical operations, it is often better practice to close the db instance after the commit and acquire a fresh one from the pool for the next read operation. This completely resets the session state.

[tglman]

I confirm this, the only exception is that SQL NOCACHE will not refresh the local cache when running in remote.