CODEWITHSUNDEEP
c#sqlsql-serveroracle-databaseconcurrency
Vinicius Gonçalves
Vinicius Gonçalves

Reputation: 2724

ORACLE: Simulate READ UNCOMMITTED Behavior

With Oracle, I need to allow another transaction to view uncommitted data or data that will be rolled back if the transaction that changed it does not complete successfully.

Let's look at the example:

Arrange

CREATE TABLE Car
(
   Name VARCHAR(20) NOT NULL,
   Building NUMBER(1) NOT NULL
);

INSERT INTO Car (Name, Building) VALUES ('Car 1', 0);

Act

CONNECTION 1

UPDATE Car SET Building = 1 WHERE Name = 'Car 1';

CONNECTION 2

SELECT * FROM Car;

Result:

Name       Building
===================
'Car 1'  |  1
         |

--Should be able to view the new Building value (1/true) from CONNECTION1 before 
--the CONNECTION1 transaction be commited.

CONNECITON 1

--Do a lot of work to build the car (20seconds), then sets the Building flag to 0 and commits the transaction.

It can be reached using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED on CONNECTION2 in SQL Server, but not in Oracle, it does not have this option.

I need provided end user to view that a car is beign builded and I need make sure if some fail occurs in the car build process (like energy fail), the Building flag returns to 0/false.

Upvotes: 0

Views: 350

Answers (1)

GMB
GMB

Reputation: 222412

I need provided end user to view that a car is beign builded and I need make sure if some fail occurs in the car build process (like energy fail), the Building flag returns to 0/false.

Instead of (or in addition to) using a boolean flag, why not manage a list of possible states, like:

  • 0 - new
  • 1 - build in progress
  • 2 - build failed
  • 3 - built successfully

That way, you can have the connection that handles the build update the state at the beginning and end of the process (and always commit immediatly). On the other connection, you can follow the lifecycle of the item.

Upvotes: 2

Related Questions