September 16, 2003 at 4:44 am
code please?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
September 16, 2003 at 4:47 am
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select statement
/* something here please to revert to 'normal' */
more sql statements
Jeremy
September 16, 2003 at 4:53 am
I believe it remains for that connection until it is explicitly changed.
By the way how do we check what is the current isolation level?
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
September 16, 2003 at 4:56 am
I want to explicitly change it back but what is it best to change it to?
Jeremy
September 16, 2003 at 5:23 am
Jeremy,
I understood at once what you mean, but unfortunately I was out and didn't get to read your replies until now.
Put SET TRANSACTION ISOLATION LEVEL READ COMMITTED where you want to enter "normal" state. This is the default setting. Otherwise, it is a setting that applies to connection, so as soon as that connection is terminated, everything is OK and next time you'll connect you start with the default.
Just be very careful with it when you use it on "living" data... it shouldn't cause any problems in the specific example mentioned (where the row is updated in a short transaction), but otherwise it's not recommended. Rollback of a long transaction with update/insert could leave you in a situation when you have selected something that doesn't exist in the database - because you read it before it was commited, and then it was rolled back.
September 16, 2003 at 5:32 am
Vladan,
Thanks.
I've read about phantom data but I think there is very little risk in this situation.
Jeremy
September 16, 2003 at 6:44 am
instead of setting the isolation level for the procedure try locking hints at the statement level. We use a lot of nolock or readuncommitted hints in are application. You might also try to use a another table for the status. Place a record referencing the task in a table called scheduled, this way you can insert and delete from that table instaed of updating. SQL Server does a better job of locking with inserts and deletes then updates.
September 16, 2003 at 8:54 am
I agree - we never change the transaction isolation level - but just use locking hints. Causes fewer problems with procedure nesting.
Jeremy - sorry about the incomplete message. My impression was that you may have been changing data in the row after waiting for the other process to complete. The only way that would be available would be
BEGIN TRAN
change data
COMMIT TRAN
--wait for other process
BEGIN TRAN
change more data
COMMIT TRAN
If you are not changing more data, I would still use the first COMMIT to assure that other processes can pick it up - otherwise, the default is for the transaction to hold until the procedure has completed.
Still - if you are waiting anyway - why not just call the changing procedure directly? It may require a decision procedure to know how to deal with the item in question, but it keeps the task under direct control.
Just a thought.
Guarddata-
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply