Multi User Application Database Transaction Stability

[retkehing]

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.

 

[cjard]

INSERT
If two users perform insert at the same time, may i know how to deal with these primary keys?

You dont, because its totally the wrong way to do it. Read up on how your database handles primary keys in this situation. ALl the proper databases, like Oracle, SQLServer, DB2, have mechanisms to prevent PK collisions.

UPDATE
May i know how to implement lock and unlock the record? Thank you.

That is the database's concern, not yours. Read up on how your chosen database performs record locking.

 

[retkehing]

You dont, because its totally the wrong way to do it. Read up on how your database handles primary keys in this situation. ALl the proper databases, like Oracle, SQLServer, DB2, have mechanisms to prevent PK collisions.

Is that what you meant i can't generate the primary key for them? However this is part of user requirements, for example they need the emp no. to be E00001 and so on. So the system will just count the record stored in the database in order to generate an unique primary key. When the user delete a particular emp profile, then only its record status will be updated to Deleted but it will still be stored in the database. No matter, the total count of the record will always be unique but now i am looking into the Concurrent Transaction Problem. Thank you.

 

[cjard]

Sorry, but that's not what I meant.

When I wrote:

You dont, because its totally the wrong way to do it. Read up on how your database handles primary keys in this situation. All the proper databases, like Oracle, SQLServer, DB2, have mechanisms to prevent PK collisions

I meant:
You dont, because its totally the wrong way to do it. Read up on how your database handles primary keys in this situation. ALl the proper databases, like Oracle, SQLServer, DB2, have mechanisms to prevent PK collisions


Because you never bothered to say what database you were planning on using, I didnt really feel like launching into an explanation of every PK collision mechanism in every RDBMS out there.

At the risk of repeating myself, may I suggest that you read up on how your database handles primary keys in this situation?

Here are some googles:
oracle sequences
sql server identity
access autonumber

 

[retkehing]

I make use of the SQL Server.

 

[cjard]

You know what's coming next, dont you?