How to lock record when inserting
-
Hi, I have more than 10 users inserting records at the same in one table throughout the day and most of the time I have problem mixing data. I have a header/detail entry form web page. My insert occurs when the users click the Submit button, it will SELECT the next available number from DB and use it as my Primary key to insert record (header/detail). Most of the time 2 users get the primary key at the same time. If you could redirect me to the site with has the solution in locking or help me in my problem My Solution but sometimes it failed.
bool blnExist = false;
while (!blnExist)
{
string sKdey = PopulateDraftNumber();
if (IfKeyExists(sKdey))
{
blnExist = false; //found record, reselect another key
}
else
{
blnExist = true; //found no record insert into table
lblRequestNo.Text = sKdey;
INSERT RECORD INTO TABLE
}
}Dabsukol
-
Hi, I have more than 10 users inserting records at the same in one table throughout the day and most of the time I have problem mixing data. I have a header/detail entry form web page. My insert occurs when the users click the Submit button, it will SELECT the next available number from DB and use it as my Primary key to insert record (header/detail). Most of the time 2 users get the primary key at the same time. If you could redirect me to the site with has the solution in locking or help me in my problem My Solution but sometimes it failed.
bool blnExist = false;
while (!blnExist)
{
string sKdey = PopulateDraftNumber();
if (IfKeyExists(sKdey))
{
blnExist = false; //found record, reselect another key
}
else
{
blnExist = true; //found no record insert into table
lblRequestNo.Text = sKdey;
INSERT RECORD INTO TABLE
}
}Dabsukol
Generating the Key while inserting should be the responsibility of the server at the insertion time. I mean what you do is: 1- Ask the server for a key -In a very poor performance way by the way!- 2- Attach that key to my Record 3- Send record to server So between step 2 & 3 another clients gets the same record. The right thing to do is 1- Send data to server without a key 2- The server generates a key for the new record 3- The server locks the table for update 4- The server inserts the record in the table 5- The server unlocks the table If you're using ADO/SQL, Then define your key comlumn type as IDENTITY. It will auto increment by itself upon insertion of new records. Otherwise you'd have to do it manually.
Regards:rose:
-
Generating the Key while inserting should be the responsibility of the server at the insertion time. I mean what you do is: 1- Ask the server for a key -In a very poor performance way by the way!- 2- Attach that key to my Record 3- Send record to server So between step 2 & 3 another clients gets the same record. The right thing to do is 1- Send data to server without a key 2- The server generates a key for the new record 3- The server locks the table for update 4- The server inserts the record in the table 5- The server unlocks the table If you're using ADO/SQL, Then define your key comlumn type as IDENTITY. It will auto increment by itself upon insertion of new records. Otherwise you'd have to do it manually.
Regards:rose:
Thanks, I have existing table with more than 50K records and it is running live at the moment. For now, I can't modify the table to have another columns with type IDENTITY. I have two step in inserting records. First is where I have the problem to get the TMP key, once everything is finalized I users will click the SUBMIT button. it goes to step two. Second step is to update the key to its original format "yymm-sequence" and I am already using your recommendations. What I want is a locked statement. 1- Ask the server for a key -In a very poor performance way by the way!- (What is your recommendation, I generate akey on my own? without connecting to the DB?) 2- Attach that key to my Record
-- I WANT SOME SORT OF LOCKING HERE ON THIS PART BEFORE STEP 3 ---3- Send record to server Thanks.Dabsukol
-
Thanks, I have existing table with more than 50K records and it is running live at the moment. For now, I can't modify the table to have another columns with type IDENTITY. I have two step in inserting records. First is where I have the problem to get the TMP key, once everything is finalized I users will click the SUBMIT button. it goes to step two. Second step is to update the key to its original format "yymm-sequence" and I am already using your recommendations. What I want is a locked statement. 1- Ask the server for a key -In a very poor performance way by the way!- (What is your recommendation, I generate akey on my own? without connecting to the DB?) 2- Attach that key to my Record
-- I WANT SOME SORT OF LOCKING HERE ON THIS PART BEFORE STEP 3 ---3- Send record to server Thanks.Dabsukol
Nader Elshehabi wrote:
3- The server locks the table for update
It's the server's problem, not the disconnected client to generate & implement the key. That's what is making you problems. You should re-layer you design. If your DB is on a local network, I suggest upgrading it to another level. You're talking about 10 persons currently, but I believe you should think of the right way to do it. You already go 50K records we don't want to lose that if things got nasty!! My suggestions:- 1- Take another look at your software design -Client/Server layers- 2- At least upgrade your table be calling an
ALTER TABLE ADD COLUMN NewIndex IDENTITY(1,1), & shut down the work for upgrading at least an hour. You can do it like this: 1- Make a test local database 2- Fill it with sample data 3- Upgrade the code of the client & the database to handle the new key 4- Try the upgrade 5- Shut down the original DB for 10 mins for maintenance, and Upgrade both DB & all clients. 6- Everyoneishappy = true;& no more bugs :)Regards:rose:
-
Hi, I have more than 10 users inserting records at the same in one table throughout the day and most of the time I have problem mixing data. I have a header/detail entry form web page. My insert occurs when the users click the Submit button, it will SELECT the next available number from DB and use it as my Primary key to insert record (header/detail). Most of the time 2 users get the primary key at the same time. If you could redirect me to the site with has the solution in locking or help me in my problem My Solution but sometimes it failed.
bool blnExist = false;
while (!blnExist)
{
string sKdey = PopulateDraftNumber();
if (IfKeyExists(sKdey))
{
blnExist = false; //found record, reselect another key
}
else
{
blnExist = true; //found no record insert into table
lblRequestNo.Text = sKdey;
INSERT RECORD INTO TABLE
}
}Dabsukol
If you can't use an identity column, a common pattern is to have a sequence table that contains the next sequence number for your table. Then, your code creates a transaction, updates the table to add 1 to the sequence number and then select from the table. The trick here is that the transactional update will lock the sequence row and then you select the record to retrieve this value - the beauty here is that the update lock prevents anybody else getting to the record until you commit the transaction.
Deja View - the feeling that you've seen this post before.