MTS Transaction Isolation Levels

[hmorgan]

We are using MTS on NT 4, and have dll's written in VC++ running under MTS, they use a MS SQL Server 7 database, via ole db.

Please can someone explain how to set the transaction isolation level, so that read locks on the database aren't held for the duration of the transaction.

Thanks for any advice,

Henry

 

[SemperFiDownUnda]

You realise that if you play with the isolation levels then you get the risk of doing dirty and phantom reads!

If you have an object that is executing for that long and holding resources you either have to live with that or look at improving you model to only lock resources when needed.

Make your objects stateless is the biggest difference you can make. The only time I've wanted to break the ACID property is when having a status display system. Since it didn't change data and would get updated later to the correct state there isn't a problem.

SQL defaults to read committed.

You can use "Set Transaction Isolation Level" to change the level for the life of the session. Or you can in your statements use a table-level locking hint with a "WITH" clause attached to the table name in the "FROM" clause.

with the "Set Transaction Isolation Level the options are
Read Uncommitted
Read Committed
Repeatable Read
Serializable

Hope this helps

 

[hmorgan]

Thank you.

Is there a way that you can set it under MTS, rather than having to have an extra SQL statement at the beginning of each transaction?

 

[SemperFiDownUnda]

If you are talking MTS transactions you are out of luck. MTS does not support these isolation levels as they are not standard. MTS Transactions are built upon DTC which will work across many different types of transactional systems. As long as the resource supports 2 phase commit (2PC) then MTS will support it.

So you could have a Oracle Database running on a DEC server, a transaction Filesystem running on LINUX and a message que running on windows and you can roll them all back if something goes wrong.

Breaking the ACID is VERY bad in a 2PC distributed system.
If you are trying to do this because of deadlocks or long blocking times you should look at the cause of the real problem instead of covering it up. It will come back to bite you in 99% of cases.

 

[hmorgan]

OK, thank you.