×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

MTS Transaction Isolation Levels

MTS Transaction Isolation Levels

MTS Transaction Isolation Levels

(OP)
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

RE: MTS Transaction Isolation Levels

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

RE: MTS Transaction Isolation Levels

(OP)

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?

RE: MTS Transaction Isolation Levels

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.  

RE: MTS Transaction Isolation Levels

(OP)
OK, thank you.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close