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 |
|
rameshsee
Starting Member
1 Post |
 Posted - 2002-11-09 : 00:33:39
|
| hi,I am using VB as frontend and MSDE as backend DB for an Client/server architecture.i am using an query as below,rs.open "Select * from exam with (HOLDLOCK) where exmid='EX00000001'",cn,adOpenStatic,adLockPesimisticThe above query is not locking the selected row in MSDE. i want this to happen, other users should not be able to access this record when one person is editing this record. When the same query is executed for a Database in MS SQL , the record is getting locked.In MS Sql server, i am able to lock the entire table / lock certain rows. But the same is not possible in MSDE.Can any body please help me out in it. Does MSDE supports rowlock (once i had read in a document that both SQL server and MSDE supports ROWLOCK?.If so, how it implement in VB 6.i will really appriciate any suggestion.ramesh.c |
|
|
solart
Posting Yak Master
148 Posts |
Posted - 2002-11-18 : 17:36:46
|
| Ramesh I don't know the direct answer to your question, but I am wondering if the technique used should be modified to not require the locking in the first place.It seems to me that using the current technique, a user could retrieve the data, then have their work interrupted by a phone call, maybe they need to go to the bathroom, get called away to a meeting, etc. This then could leave the data locked indefinitely?????Can others offer a better design solution?From my perspective:First since you are using MSDE I assume you don't have a lot of users using the application/database (maybe a max of 5 or 6). Still if a row is locked for a long period of time, this could be a real inconvenience.Add a column to the table which has a datatype of timestamp (A database-wide unique number that gets updated every time a row gets updated).Application then retrieves row or selected columns (and includes the column which has the timestamp). User edits data. While the Update statement can not modify the column containing the timestamp (SQL Server will do this), it can check the column to compare its value against what was originally retrieved. Update is only allowed if compare is equal. If timestamp is not equal, then this indicates that someone else has changed the record since your application originally retrieved it. Therefore, the application notifies the user that update failed due to a very recent change and asks the user to perform the edit/update process over.HTHs solart |
 |
|
|
|
|
|
|
|
help in Lock handling in MSDE