Dear Sir/Madam,
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 have just finished reading your book, the one about performance and tuning of Sql Server and found it very helpful. It covers areas which I did not know and definitely enhanced my knowledge about the internal workings of SQL Server. I have a more spherical knowledge now.
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!!!
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 have just finished reading your book, the one about performance and tuning of Sql Server and found it very helpful. It covers areas which I did not know and definitely enhanced my knowledge about the internal workings of SQL Server. I have a more spherical knowledge now.
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!!!