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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-24 : 09:05:52
|
| Dirk Bolier writes "HiI would like to lock a specific record in a table when a user reads it. This will only allow this user to change the data in that record which is locked for his/her use only. Other users must be able to read that locked record, but have no access to change or delete it, until further notice (when the first user releases the lock).I am using Delphi as my developement platform and would like lock and unlock the record in SQL SERVER2000(SP 1 and 2) manually.Hope someone can help me with this problem.RegardsDirk Bolier" |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-24 : 09:10:44
|
| Read up on application locks in BOL.Jonathan Boott, MCDBA |
 |
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-06-24 : 09:33:40
|
| Difficult question to answer. It should be possible to implicitly lock the current record in a client side cursor (i.e. ADO's MoveNext, Edit and Update method will release it ) I don't use delphi, although I heard it has a good data access toolkit via InterBase.ADO also allows you to specify locking behavior via adUsePessimistic and adUseOptimistic settings, basically allowing you to lock the row on the fetch or when the update is applied. The optimistic setting is good for those wanting to error trap changes to the record since it was last fetched.You may also have to prespecify page level or row level locking before you create the client connection. Is that right guys??I havent used ADO for over a year now preferring to use ODBC direct, so please don't take as red.Dan |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-06-24 : 09:54:14
|
quote:
ADO also allows you to specify locking behavior via adUsePessimistic and adUseOptimistic settings, basically allowing you to lock the row on the fetch or when the update is applied. The optimistic setting is good for those wanting to error trap changes to the record since it was last fetched.
To expand on Dan's comments:Pessimistic locking means that the row currently visible by the client-side cursor will be locked by ADO. Optimistic locking means that ADO keeps track of the "old" column values for any given row and uses those old values to make sure another process has not updated the row before you do.The larger issue here though is whether you truly want database locking. In small systems this is usually preferable as (more than likely anyway) there is just one entity sending data updates. In larger, enterprise-scale systems, this is usually not preferable because there are many entities sending updates, and a good amount of data modification does not happen as a result of user interaction. In that situation you would not want database locks applied to the row, blocking other processes, but rather an application locking scheme.Jonathan Boott, MCDBA |
|
|
|
|
|
|
|
|
Set a lock on a record manually