Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BigSam
Starting Member
30 Posts |
 Posted - 2006-03-22 : 10:14:51
|
| We are looking at a web application that can connect to remote database servers. These remote database servers also support a 'local' application. (Both applications can update the same database.)When the web application issues a select with a NO Lock, what memory is being queried - memory on the web server or memory on the remote server?What happens if the queries are converted to stored procedures on the remote server & the web server runs it?ThanksBigSam |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-03-22 : 10:39:20
|
| I think you may be confusing several different concepts.when a SELECT statement is processed by Sql Server (whether as inline code from a web-server or as statements contained in a stored procedure) the results come from persisted data from within the database server. When the the NOLOCK locking hint is specified it just means that the data could be coming from database transactions that have not yet been committed. It has no bearing on who submitted the request or where it came from. I'm not sure what you mean by: "memory on the web server or memory on the remote server". It is possible to cache data (usually static data like lookup values) in an application's memory to reduce calls to the database but based on your "nolock" question, I don't think that's what you're talking about.Be One with the OptimizerTG |
 |
|
|
BigSam
Starting Member
30 Posts |
Posted - 2006-03-22 : 11:03:59
|
| When the select statement with no lock is issue you say 'the data could be coming from database transactions not yet committed'. Where does that data reside?BigSam |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-03-22 : 12:18:51
|
| Still from the database tables. Imagine this scenario. Lets say there is a stored procedure that (within an explicit transaction) inserts a row to a parent table and then inserts 3 rows to a child table. If no errors occur and no business rules were violated then the transaction is committed otherwise it is rolled back. Now if a different user selects from the parent table after the insert to the parent but before the transaction is committed they will not see that newly added row under the default isolation level. However if they specified a nolock locking hint then they would see that row from the uncommitted transaction.If an application has collected user data but is waiting for the user to push a submit button that data will not be seen by anyone else issueing selects to the database. The database is unaware of those rows.Be One with the OptimizerTG |
|
|
|
BigSam
Starting Member
30 Posts |
Posted - 2006-03-22 : 12:57:52
|
| I think that clears things up for me. Thanks much.BigSam |
|
|
|
|
|
|
Dirty Read Question