I am currently developing a multi user VB.NET 2005 application and this is my first time to involve in multi user application. Anyway, i know that multi-user application is not as easy as standalone application. I need to take care of the database stability for each and every user. My concern is may i know how to build up this stability for each and every transaction in terms of Data insert and update? This database resides on server and the user is able to access the data through the client application.
INSERT
The primary key is generated by application when the user enters into the data insert screen and it is done by counting the total amount of records in database. If two users perform insert at the same time, may i know how to deal with these primary keys? I am sure my application must be able to generate two different primary keys for these two users based on the time gap e.g. 0.001 milli second for the first user who enters into the screen and occupy the first primary key than the second user. But i have no idea on how to translate this to code.
UPDATE
If a particular record is viewed by a user and that user peforms an updates on the record while another user updates it at the same time, i may need to figure out a solution for this else the database will really be crashed. Is there any suggestion on it? May i know how to implement lock and unlock the record? Thank you.
For the INSERT part, you can't really generate keys like that from the client side without locking the entire table on the server. You can look at use of IDENTITY column for example which allows you to generate sequential numbers on the server. The server handles the concurrency part. There are other ways to generate keys on the client like using GUID for example. But all of these depends on the type of application you are writing and your data model.
For the UPDATE part, when one connection is updating a particular row or rows then any other connection that tries to update the same row(s) will be blocked. So the concurrent updates will happen in serial fashion depending on which ran first.
My suggestion would be to look at some of the topics on concurrency, locking, transactions in Books Online to get an idea of what the database engine can do. You should also get a book on SQL Server that describes some of these concepts so you don't try to invent or reinvent elaborate techniques on the client which might be suspect at best.
