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 |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
 Posted - 2002-09-04 : 08:10:29
|
| I am firing the following query in the query analyzerUse pubsbegin tranupdate employee set fname='ravi' where emp_id='PMA42628M'Without committing (commit tran) I am opening another sessionin query analyzer (new window) and fire the following query,use pubsselect * from employeeThe above query will not show any output .But modifying the above query using ‘nolock’ will give the outputUse pubsselect * from employee (nolock)But in the result name ‘ravi’ is shown, even though the transaction is not committed.Following are my doubts1>why SQL Server is not allowing me to select the data in the second session? (without using ‘nolock’)2>When used ‘nolock’ why it is giving the new value (uncommitted) instead of the old one.3>How to get the uncommitted value?4>what will happen in case of Transactions? For e.g.a> In session 1 ,user 1 has modified the salary but not committed.b> In session 2, user 2 has calculated the bonus depending on salaryc> In session 1, user 1 has roll backed the transaction to get the old values.d>Also in the case of backup, The second session will backup the data with new value. Eventhough the transaction was uncommitted. If the database crashes at this point of time, and this backup is restored having a new value of the transaction which is not comitted,the backup gives eronious info.how to getrid of this problem?Thanks in advance….. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-04 : 08:55:13
|
| In general do not use nolock except when just viewing data by dba's / developersin which case always use nolock or set the transaction isolation level to prevent deadlocks.1The whole idea is that you can view data that is consistent in the databaseThe uncommitted transaction may have other updates so that if you access different data they may be inconsistent and you will be getting an eroneous picture.Consider a join - if an entry is added to one table but not the other (due to being in the middle of a transaction) should you receive the data - you may even try to correct the situation and add duplicate records.sql server solves this by locking the updated record until the transaction is committed or rolled back.2nolock means do not honour locks and read dirty data. The data has been updated - you are just able to access it by avoiding the lock taken.3? you already have it.4Transactions only force consistent data at the time of access.If you want the data you read to still be applicable when you update then you will need to lock the data or stop updates in some other way.b will either be blocked until the transaction completes so will be ok.Transactions will not prevent poor application design - session 2 needs allow for data being updated during the calculations or prevent it somehow.BackupsNope - backups take care of transactions (you should expect this otherwise sql server would be pretty useless).Any uncommitted transactions will be rolled back as the last step of the restore.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
SKIBUM
Starting Member
32 Posts |
Posted - 2002-09-04 : 10:48:57
|
| You're forgetting the COMMIT TRANSACTION or ROLLBACK TRANSACTION Commands...try something like this. You must complete the BEGIN TRANS command with either a COMMIT or ROLLBACK.Use pubs begin tran update employee set fname='ravi' where emp_id='PMA42628M' if @@ERROR = 0 BEGIN COMMIT TRANSACTIONENDELSE ROLLBACK TRANSACTIONHave fun! |
|
|
|
|
|
|
Transaction...