Lock the Transactions between multiple users

Posted by Ganeshsvelu under Sql Server on 11/20/2010 | Points: 10 | Views : 1844 | Status : [Member] | Replies : 5
Hi,

In my application creating rollno for students.Multiple users involved to generating rollno's.In my old stored procedure am not used any lock keys in main table
so duplicate rollno occured for multiple students(same roll no).So after i used in my table Tablockx Key.Its working properly.But Application acts very slow no one
can generate roll no's.
So help me which Lock key i will use or any other Solution is there for this kind of situations?
Reply pls.....


Regards,
Ganesh




Responses

Posted by: SheoNarayan on: 11/20/2010 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
User lock keyword of C#.

object obj = new object();

lock (obj)
{
// do all your work to insert or update
}


This ensures that only one thread access the block of lock so there is no chance of duplication.

Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Ganeshsvelu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 11/20/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Ganesh,

If user can not create the rollno means set identity in the column.
nRollNo int identity(1,1)

identity --> Automatically rollno added in your table.

else
find out the max roll no in that table and add one roll no
select isnull(max(nRollNo),0)+1 from table


Try this way...

Cheers :)

Thanks,
T.Saravanan

Ganeshsvelu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 11/20/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

When you use "TabLockx" table hint, It definitely place a Exclusive(x) lock on your table. Other trabsaction should wait until the existing complete.

So, As 'T.saravanan' said, IDENTITY can give you a sollution.

1. "RollNo" In your "students" table can be an IDENTITY.
2. When insert a new student's information, You should pass all the values except the IDENTITY column (RollNo), so the "RollNo" will be automatically generated and inserted on behalf of you.
3. You can use these Insert activity in a Stored Procedure.
4. Return the Generated/Inserted Identity values(using @@Identity) as a RETURN / OUTPUT from stored procedure and use the IDENTITY value in your application...

Note:
1.To denfine an IDENTITY on your existing table, You should DROP the table and Recreate the same with IDENTITY.
2. If you want to maintain the existing data then, Just copy the data to some other table....
3. Create the Student table with IDENTITY and force the data from the backup table to Student table.
ie:
SET IDENTITY_INSERT Student ON

INSERT Student(RollNo,Column1, Column2,...)
SELECT RollNo,Column1, Column2,... FROM Student_Backup
SET IDENTITY_INSERT Student OFF
4. Now you can proceed your own way...

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Ganeshsvelu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ganeshsvelu on: 11/20/2010 [Member] Starter | Points: 25

Up
0
Down
hi,

Am not getting rollno in single table.It can be concatenate from four tables based on some conditions.In that four tables only one table
as been important for generating rollno.
Example:
Table1:99
Table2:10
Table3:04
Table4(Important Table):1000
Set Rollno : Table1+Table2+Table3+Table4

After Got Rollno updated in Table4 as 1000+1 like that
So i will put TablockX Keyword in Table4 at the time selecting.

Pls Reply based on this requirement



Ganeshsvelu, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Deeraj on: 11/21/2010 [Member] Starter | Points: 25

Up
0
Down
Would you mind posting your code? Also, Your requirement needs more explanation.

Ganeshsvelu, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response