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 |
|
Kristen
Test
22859 Posts |
 Posted - 2004-10-15 : 01:15:48
|
| Following a seed sowed by Lumbago ...What locking hints should I be using?I reckon there are 3 scenarios that we currently have in our code:1) Get me the current data. Usually a single row, or a small number of rows (the lines on an order, perhaps). I just want committed data, I don't want to lock the data (i.e. no synchronised update is following); I may want to ensure that i do NOT get escallated to a table lock; most likely I do not want to get blocked by another process (I just want whatever data is "current", even if it is in the process of changing)We just do a SELECT ...1b) What about when I do care about data this is "in the process of changing"?2) I want to update a record. I don't care too much about the current data in the record (i.e. we will do a pessimistic (or should that be "optimistic"??) WHERE clause with all the original column values to ensure the record is unchanged by another user)This will ALWAYS be inside a BEGIN TRANSACTION and then we will conditionally issue a COMMIT or ROLLBACK - the ROLLBACK will be issued if we have a problem, possibly with a subsequent UPDATE/INSERT or a subsequent logic test failsWe just do an UPDATE ... WHERE (lots of column checks)I will be assuming that the data in the ROW that I have changed will not be changed by another user UNTIL I have done my COMMIT (or ROLLBACK) (although that's pretty rarely actually acted on - an example would be where I do a second UPDATE to the same record, this time perhaps only using the PK columns in the WHERE)Do I need a lock for that?3) Sometimes I do a SELECT and then an UPDATE based on what I findA simplistic example ('coz it could be done using CASE) would beSELECT @foo = foo FROM bar WHERE foobar = 1IF @foo < 50 UPDATE bar SET foo = @foo+1 WHERE foobar = 1IF @foo > 50 UPDATE bar SET foo = @foo-1 WHERE foobar = 1I'm presuming here that I do NOT want to allow the row to be updated externally between SELECT and UPDATEIs it sufficient for me just to wrap this in a BEGIN TRANS / COMMIT block? Or do I need to explicitly provide a lock hint?I think that covers the scenarios that we cater for - are there others I should worry about?Kristen |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-10-15 : 07:04:03
|
| >> I just want committed data>> I do not want to get blocked by another process >> I just want whatever data is "current", even if it is in the process of changingCan't be done without implementing row versioning. The data will be updated and locked by an updating process and you can't get committed data for that row until the update is committed or rolled back.Using a nolck hint will get the uncommitted changes and potentially invalid data.When you update a row inside a transaction then the row is locked until the transaction completes. Other spids cannot access the row without using nolck which will get the data you have changed.==========================================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. |
 |
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2004-10-15 : 17:38:36
|
| FROM BOLAdjusting Transaction Isolation LevelsThe isolation property is one of the four ACID properties a logical unit of work must display to qualify as a transaction. It is the ability to shield transactions from the effects of updates performed by other concurrent transactions. The level of isolation is actually customizable for each transaction.Microsoft® SQL Server™ supports the transaction isolation levels defined in SQL-92. Setting transaction isolation levels allows programmers to trade off increased risk of certain integrity problems with support for greater concurrent access to data. Each isolation level offers more isolation than the previous level, but does so by holding more restrictive locks for longer periods. The transaction isolation levels are: READ UNCOMMITTEDREAD COMMITTEDREPEATABLE READSERIALIZABLETransaction isolation levels can be set using Transact-SQL or through a database API:Transact-SQLTransact-SQL scripts and DB-Library applications use the SET TRANSACTION ISOLATION LEVEL statement.ADOADO applications set the IsolationLevel property of the Connection object to adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead, or adXactReadSerializable.OLE DBOLE DB applications call ITransactionLocal::StartTransaction with isoLevel set to ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, or ISOLATIONLEVEL_SERIALIZABLEODBCODBC applications call SQLSetConnectAttr with Attribute set to SQL_ATTR_TXN_ISOLATION and ValuePtr set to SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ, or SQL_TXN_SERIALIZABLE.See AlsoIsolation Levels in SQLOLEDBSET TRANSACTION ISOLATION LEVEL*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-16 : 01:38:14
|
| Thank Nigel. Actually [despite what I typed!] I'm not unhappy to be blocked by an update that is in process - I'll have the fresher data as soon as it is committed (or the old stuff if a rollback!)As our UPDATES are efectively locking the row I'll stop worrying about whether I need any other specific locking mechanics, looks like I'm OK.Thanks tuenty, I'm familiar with isolation level, but I don't really want to go playing with that hither-and-thither!Kristen |
|
|
|
|
|
|
|
|
Locking hints