Click here to Skip to main content
15,040,003 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello Everyone
i m a fresher.
I have one small online form submission application.
Thousands are user filling their data in it.

i m facing one issue ...i have fired two queries in my program in which ..in one query the max id +1 of a table is found out and and that value is being passed out to another query to insert the details of the User.

When Two users almost on the same time are submitting their data then the max id which is found out by first query is being shared by both the users.

in normal circumstances application works fine .

Will Thread.Sleep will be my answer..?

Waiting 4 reply..!!
Posted
Comments
bhagirathimfs 16-May-12 7:04am
   
One suggestion: Always make the primary key (Example:here ID) auto incremented. So that no need to write a query to find out the last id.
vaibhav mahajan 16-May-12 7:40am
   
RIGHT bhagirathimfs I ACCEPT UR SUGGESTION...BT..WHAT SHUD I DO NOW..?..IT IS ALREADY HOSTED ON TO THE SERVER..N USERS ARE USING IT..!!

"Will Thread.Sleep will be my answer"
No. All that would do is move the problem to collide with a different user.

There are a number of ways to handle this. The simplest in terms of implementation is to set the ID field to Identity - which means that SQL becomes responsible for allocating it a value, and you just have to modify your code to not try to set it. However, this has the drawback that you need some means of finding out what value was assigned if you need to cross reference this to any other tables. This means moving your insert into a Stored Procedure which does the insert and returns the Id value in the same operation, or reading the record back after the insert to find out what value was assigned. Which means you need to have a way to find it as a unique row...

The other method is not to use a integer ID, but to use a Guid instead. You assign the Guid ID in your application, and set it when you insert the row. Since the chances of generating two identical Guids is slightly lower than those of winning the lottery every single week for a few years it is a simple way to do it, and is generally the approach I take. It can mean more extensive changes to your software though, depending on how well you wrote it in the first place! :laugh:
   
Comments
Wendelius 16-May-12 16:50pm
   
Very good answer
You need to use Transactions - either at code level or DB level based on your workflow and need.

Here:
MSDN: BEGIN TRANSACTION (Transact-SQL)[^]
SQL Server Transactions and Error Handling[^]

MSDN: Local Transactions[^]
Using Transactions in ADO.NET[^]
   
Comments
Wendelius 16-May-12 16:50pm
   
Yep!
Sandeep Mewara 17-May-12 1:34am
   
:)
Use transaction into your code or in stored procedure return a value using @@identity
   
Comments
Wendelius 16-May-12 16:50pm
   
Agree
Use Transaction and auto increment for the column. Also execute both queries in one transaction.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900