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 |
|
mushtaq
Starting Member
2 Posts |
 Posted - 2002-09-02 : 01:01:55
|
| Hi, I am working on client/server programming (visual basic and sql server). I need help on multiuser database programming. Could anybody help me in this area. Please give me the reference where i can refer for that. Basically i need help on locking mechanism. if possible send me the sample program (in visual basic).Thanks in advanceMushtaq |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-02 : 08:11:08
|
| Decide on locking by what the app needs to do, how volatile the data is and how many users.Commonly client server apps implement optimistic locking.I will assume that all database access is via stored procedures and that you will call a single stored proc for updates i.e. all transaction control is within the SP.Optimistic locking means that you assume that the update will work and that no other user will change the dat after the read and before the update. If this happens you will return an error to the user.Put a timestamp column on all the tables. This will be updated on all updates in line with the current timestamp.When the client reads the record/records for update it also gets the timestamp. On update it sends that timestamp back to the stored procedure which checks it against the record - if it is the same the update can go ahead. If it is different then the update must fail and the user told to retry.begin tranif exists(select * from tbl where recid = @id and tstamp = @tstamp)begin update tbl ...endcommit tranthis will not work (probably) as two clients can succeed on the existence check and then overwrite each others updates.this will workupdate tblset ...where recid = @idand tstamp = @tstampif @@rowcount <> 1 or @@error <> 0 raise errorcommon method is to create another table and lock that exclusively while you check the timestamp and release it on update completion so that you block updates but allow reads.Variationscreate a table to hold the table id and rec id.Teh client reads for update and the SP puts the id's and spid and spid logon time in this table.It does not allow the table/rec IDs to be obtained by two active spids. The entries are cleared on update/cancel.this assumes that you have single pemanent connections for the app (i.e. client server).==========================================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. |
 |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-09-02 : 10:25:48
|
| Oh my god! Another kamikaze programmer! |
|
|
|
|
|
|
Help me on multiuser database programming ?