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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-05-17 : 09:46:49
|
| Muhammad Nazim writes "Development Environment: Visual Studio .Net 2003Database: SQL Server 2000My question is about SQL tranactions, say we have a application designed to take input from 50 users, we have a nwtwork of 50 users AND one SQL server.When a user opens the input form there is an ID generated and shown in the form as readonly field. ID is a column in a table, we fetch maximum Id value.Now all users have opened there input forms and get same ID, now when they press save to save there input. A stored procedure is called in SQL server, in that procedure we again get the maximum ID and execute Insert statement. This will add the record.My question is that if 50 users simultaneously press the save button to save there input what will happen? What I am observing is most of the users get Primary Key voilation message because the ID the stored procedure passed to insert statement was already entered by another transaction.Bye" |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-17 : 10:19:18
|
| We tend to use ZERO in this instance. When a user tries to save a record with an ID = 0 then the application (a Stored Procedure in our case) intercepts that, discovers the next available ID number, and allocates that.Alternatively set up the ID column as an IDENTITY and the number will be allocated automatically when you insert the row.In either case the user cannot know the row number until AFTER they have saved the new record.Kristen |
 |
|
|
Maux Maux
Starting Member
10 Posts |
Posted - 2006-05-17 : 14:33:43
|
| I push the IDENTITY solution myself. This situation is sort of what IDENTITY and SCOPE_IDENTITY() were specificaly made for. |
|
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 2006-05-17 : 23:02:54
|
| if you need the id first you can:a. insert a blank record with session id, to generate the identity value; use scope_identity to fetch it; then update later on when user hit save using that id.b. create a function similar to ORACLE'S synonym (sql2005 synonym or NEWID()) that generates unique id every call.but i'd follow what kristen is suggesting, user don't need to know the primary key unless it's acting like an SSN. If that is the case, you better go with a stored procedure/function.May the Almighty God bless us all! |
|
|
|
|
|
|
SQL Transactions