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 |
|
reggiestarosa
Starting Member
2 Posts |
 Posted - 2003-07-21 : 14:49:11
|
| I'm using Dbexpress in delphi and my server is sql server 2000. I need help on how to do a table row lock when updating a record. I tried this code to do a rowlock in sql serverBEGIN TRANSACTIONSELECT * FROM EMPLOYEESWITH(HOLDLOCK,ROWLOCK)// this will lock the row while ur doing the updates hereCommit Transaction// once you click the commit transaction it will release the lock.What i wanted to do is to show a message to the users that im updating this record, please try again. //Then the record that the user was trying to edit will rollback to the original record. |
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2003-07-21 : 15:26:55
|
| Not telling you how to do your job but its generally considered bad practice to leave control of a transaction (and hence a lock on a record/page/table) up to the client. What if user A grabs a record, issues a lock and takes off for lunch before committing their change? On top of that I don't think (I could be completely wrong here) you can get SQL Server to respond to events from a client application, you can't issue your lock and wait for a "commit" action from your front end. i.e.begin transelect whatever from wherever (with holdlock, rowlock)-- recieve confirmation from client code hererelease lockcommit tranYou can implement, using timestamp columns or other means, optimistic concurrency by checking a timestamp column at the time of the data retrieval and then again at the time of the update. If the client can't find it's timestamp then the row has been updated in the interrim and should prompt or otherwise note that a change has taken place.m2c,Justin"Look at it out there orbiting like it's so cool, we will rule it with an army of replicants!" |
 |
|
|
reggiestarosa
Starting Member
2 Posts |
Posted - 2003-07-22 : 08:11:16
|
| any sample code that you can show to me? |
|
|
|
|
|
|
Row lock when updating a record