Transaction Issue

[megmogg]

Hi all

Just wanted to ask for some advice for a locking issue I have come across.

I am developing in FoxPro, but issue SQL Pass Through statements to SQL Server 2000.

I update Table A and it has a relationship/foreign key back to Table B.

What I have noticed is that if an error occurs during this update, a message box is displayed which stopped the routine. Then I noticed that Table B is locked. I cannot query it at all.

Therefore, during the UPDATE, the whole of Table B is locked.

As it is a multiple user environment, I still need other users to be able to query the database.

What options are available to me?

Thanks

 

[vongrunt]

How are you initiating transaction? With client-side (a la ADOConn.BeginTrans), or also in pass-through SQL (BEGIN TRANSACTION)?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[megmogg]

with a pass-through.

it also has a ConnectTimeOut of 5 and a QueryTimeOut of 5.

 

[vongrunt]

SET XACT_ABORT ON should do the trick... I think.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[megmogg]

Sorry, dont think I made the question clear.

I know what you mean and this is useful. Trouble is the error would be internal to my application and not SQL and I dont have the unique handle back to sql to cancel the transaction, this only happens when the application closes.

Think what I am looking for is: should I set my other queries to be dirty reads and what is the best way of doing this?

Also, can you get better performance from dirty reads.

 

[SQLDenis]

>>should I set my other queries to be dirty reads and what is the best way of doing this?


you do this by using the NOLOCK query hint

select * from employer with (nolock)

Dirty reads are very dangerous especially if you are using the results to update another table

>>Also, can you get better performance from dirty reads.
Yes but with a price (data integrity, see above)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Transaction depending on client is generally not reliable. What if Windows go BSOD? Or IP stack crashes? Or user goes for coffee&donuts shortly before error dialog appears in the middle of transaction?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[megmogg]

Thanks for the reply

Know what you are saying, I need to look at the forms myself to ensure nothing is effected.

As all my standard queries use just one connection/handle back to the database, could I just issue a READ UNCOMMITTED for that connection?

That way I wouldn't have to alter all my forms (if I do opt for dirty reads).

 

[megmogg]

vongrunt

yes, you are correct. I believe my only issue is the user leaves the error on the screen. If the application closes, it will release the transaction by default.

Will the querytimeout not kick in and end the transaction if the user leaves the error message on the screen?