isolation-transaction level

[fowler13]

hello there,

My name is Nikolaos Kantzelis and I am e-mailing you from Athens,Greece.

I am a developer developing web applications using ASP.Net, PHP and SQL Server.
I also develop 32-bit window applications using Delphi 7.0 and Sql Server.

I would like to ask you a question regarding a problem I cannot solve regarding isolation levels, transactions, lock hints.
I have encountered this problem.

Please bear with me for a while, as I am explaining. We are developing an application where 150 users will simultaneously hit a database. It is considered to be an OLTP system cause it is for a bank regarding real transactions with money. We need transactions and locks. But we do want data integrity and we do want performance as well. The first thing to do is tune the sql queries inside the transaction so they take little time to commit. Then we want out engineers to tune the system appropriately. I think we have achieved that but… still have transactions that contain updates that last 15 seconds. I thought to myself, not a problem. While the updates happen the other users who will request a screen that involves rows from the dataset that is being updated then they will see all rows that were committed until the moment of the new transaction. Then the system will automatically refreshes the screen the moment the transaction commits.

To test that I did this little example. I did connect to the sql server and opened query analyser and then selected Northwind db and wrote the batch below

BEGIN TRAN

UPDATE CATEGORIES
SET CATEGORYNAME='TEST1'
WHERE CATEGORYID='1'
--commit (please note without commiting)

( I realise that this will commit in milliseconds, that is why I did not commit it-assuming I had a different query inside the transaction that it will take 15 seconds to commit)

then I open a new query window(thus a new connection)
and typed

SELECT * FROM CATEGORIES

I was freezed out. I had to stop the action by hitting the red square.
When I did uncomment the line with the commit statement then I was able to do the select statement from the other query window and get results.
I was amazed. I thought that the read commited isolation level, which is the default for sql server, would show me, when I executed a select statement, all the rows that were committed just before my transaction started. I was clearly mistaken.

The closest I came to the desired functionality was using

SELECT * FROM CATEGORIES with (readpast)

But this does not give me all records.
I am sure you have realised the problem. I can not shut out the users who just request a screen (by an underlying select statement) for 15 seconds while an update takes place.

Is there any lock hint or isolation level that will allow me to get what I want. Or is it that just the sql server has his own ‘mind’ and algorithms and dynamically decides what to do and what locks will use disregarding what the user types?

Can I override that behaviour through settings or system stored procedures?

How can I resolve this ? is there any workaround?

Please help!!!

 

[camy123]

HI i will get back yo you regarding this. later on this eveneng,as i just need to do something more important.

bear with me.

 

[hmckillop]

If you look at SP_LOCK, there is an exclusive lock on the clustered index on the row, also on the index (CategoryName)
and another key for the same index
If you then try to select * from the rest of the table you will not be allowed to as exclusive locks have been granted to a row
and since you are trying to access this row with a shared lock, you will be blocked.
If you set the transaction level to be read uncommitted it will work, but will also show uncommitted data!

With just the read committed level you should be able to access any other row in the table via the same index, i.e.
SELECT * FROM CATEGORIES WHERE CATEGORYID=2

You need to read a bit more on the complex subject of locking, some articles I have found useful are on the SQL Server Performance
website and on the faqs in this site.


I would go back to the main problem though of your update taking 15 seconds though, try looking at the query plan and ensure that correct indices ar ebeing used to access the data. Typically for an OLTP system we aim for less than 500ms for execution of our largest processing procedures and they are maybe accessing tables with millions of rows.


"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.