Question
· Jun 1, 2017

Dirty read prevention - 'START TRANSACTION ISOLATION LEVEL READ COMMITTED'

I need to guarantee that a parent AND child rows has been inserted successfully before any other process is able to read ANY of the data.

What is the proper way to prevent DIRTY reads?  Per the InterSystems's documentation I should be able to use ' START TRANSACTION ISOLATION LEVEL READ COMMITTED'.  I have attempted to use these commands in embedded and dynamic SQL calls to no avail.

I have to be able to insert the data via JDBC calls, but legacy Caché (.MAC) may be reading the data, and if the data is read to quickly, I could have processing errors, as all the child rows have not been inserted.

Per the code below, every time I insert the new record my process fails.  I have also added break points and am able to view via the Global and via xDBC clients.

CRUD2(unitNumber) [unitNumber, SQLCODE] public {
    &sql(START TRANSACTION ISOLATION LEVEL READ COMMITTED,READ WRITE)
    w !, "ISOLATION LEVEL: ", $system.SQL.GetIsolationMode()
    &sql(insert into Vehicle.Parent (make, model, cylinders, unit) values ('GMC', 'Yukon', 8, :unitNumber))
    i SQLCODE '= 0 {w !, "Parent Insert failed, aborting..." q}
    i $d(^Vehicle(unitNumber)) {
        w !, "Able to read data... FAIL"
        &sql(rollback)
        w !, "Rolled back..."
    }else{
        w !, "Not able to read... SUCCESS"
        &sql(insert into Vehicle.Exterior(rimType,chromePackage,color,unit) values ('Alloy',0,'Grey',:unitNumber))
        &sql(insert into Vehicle.Interior(color,floorMaterial,powerLocks,seatMaterial, unit) values ('Charcoal','Carpet',1,'Leather',:unitNumber))
        &sql(commit)
        w !, "Committed to DB..."
    }

 

USER>d CRUD2^VehicleCrud(26)
 
ISOLATION LEVEL: 1
Able to read data... FAIL
Rolled back...
USER>
Discussion (3)0
Log in or sign up to continue

Hi P.

Check for $d(^Vehicle(unitNumber)) in your sample is not right -- process in transaction can always read data that it has changed.

Starting transaction in READ COMMITTED mode means that this transaction cannot read data modified by other transactions but not commited yet (provided other transaction properly locks the data). It does not restricted other processes from reading data changed by this transaction, unless

a) Other process also starts transaction in READ COMMITED mode
OR
b) Other process acquires the lock for the global node that is modified by current process.

So, if legacy Caché ObjectScript code does not use locks you cannot prevent it from reading your uncommited data.

If legacy Caché ObjectScript uses locks then you need acquire these locks before inserting the rows into Vehicle.* tables.

Transaction isolation in Caché is implemented using locks. When you modify data using SQL or Object-access Caché acquire locks for you, unless you explicitely say not to do this.

Please see following documentation for more information on locking and concurrency in globals, Objects and SQL
"Locking and Concurrency control"
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...
"Modifying the Database"
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

Hope this explains things a little more,
Alexander.

I'm not aware of such global setting.

If you want to prevent legacy Caché ObjectScript application with direct global access to see not-committed data, then you need to implement proper locking in that application.

Starting READ COMMITTED transaction in that process will not help as this does not affect the code that modifies globals directly.