how to prevent concurrent inserting?

tartuffe · August 14th, 2010, 05:26 PM

Hi,

Students can subscribe for any project via a webform. Table 'project ' contains all the proposed project and table 'student' contain the name of the student and the project-id.
Now, suppose there is a limit of 4 students for project 'A' and there are already 3 subscribed students for that project. One more student can choose that project. The code-behind checks whether the limit is not reached (by counting the amount students for that project in table 'student') before inserting that student in the table 'student'.

My problem is that when two students fills the webform for the same project and click on the 'save-button' exactly at the same time, the code has no time to check the limit and both students are inserted into table 'student'.

Is there a way to lock the table or something in order to preventing this?
Thanks
Tartuffe

Here my code: (vb.net)
-----------------------
Protected Sub DetailsView2_ItemInserting(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.DetailsViewInsertEventAr gs) Handles
DetailsView2.ItemInserting

Dim student As Integer
Dim comd As SqlCommand
Dim sql, id As String

' check amount of student for project 'A' in table 'student'
Using mConnection As New SqlConnection(param.ConnectionString)
mConnection.Open()
sql = "select count(*) from student where id=@id"
comd = New SqlCommand(sql, mConnection)
comd.Parameters.AddWithValue("@id", 'A')
student = comd.ExecuteScalar
mConnection.Close()
End Using

If student = 4 Then
e.Cancel = True
Page.ClientScript.RegisterClientScriptBlock(Me.Get Type(),
"myscript", _
" alert('maximum students is reached');" & _
" window.location.href='start.aspx';", True)
End If
End Sub

Protected Sub DetailsView2_ItemInserted(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.DetailsViewInsertedEvent Args) Handles
DetailsView2.ItemInserted

Page.ClientScript.RegisterClientScriptBlock(Me.Get Type(),
"myscript", _
" alert('you.are accepted');" & _
" window.location.href='start.aspx';", True)
End Sub

om_prakash · August 18th, 2010, 03:15 AM

While saving the data back to the server, you need to validate the data in database again and then either add resource to project or display error message.

the data displayed in webform might be different when it is actually stored.

tartuffe · August 21st, 2010, 10:53 AM

Thanks for replying, but i don't understand very well.
What do you mean with 'you need to validate the data in database again'?
Can you give me more details? Where to put that in the code?
Thanks

chroniclemaster1 · August 21st, 2010, 11:16 PM

Basically, the database is very sensitive and good data is like good code. Once an error creeps in, you can never really be sure you've gotten it completely out again. Therefore, you're always careful. So just because you're validating the data in the application (a very good idea), you still want to treat it carefully going into the DB. You just want that extra level of security for a production app.

Old Pedant · August 21st, 2010, 11:53 PM

Clearly, neither of those people understood what you were asking, tartuffe.

This is a common problem with any HTTP-connected client-server system. You *can't" lock the database or table, because during the time the browser user is looking at his/her screen and deciding what to do, there is NO CONNECTION AT ALL between the browser and the server.

The best you can do is one of the following:

ONE:

*AFTER* you get the request from the user to subscribe to a project, *THEN* in the ASP.NET code you can lock the table or database, ask if there are already 4 subscribed, and (if not) add the person. And then unlock. How to lock the table/database varies depending on what DB you are using. And actually, one of the easiest ways to do this is to create a Stored Procedure in SQL Server and then have the SP do the entire "check if 4, if not add this person" as a transaction. No possibility of failure.

TWO:

Use an AUTO_INCREMENT field on the table (IDENTITY field in SQL Server). (For discussion purposes let's name the field "AUTOID".) Go ahead and add the user without even checking to see if there are 4 already. *AFTER* adding the person, then get the value of the just added identity/autoincrement field by using "SELECT @@IDENTITY" (works even with Access if you use the JET OLEDB driver). Assign that @@IDENTITY to a variable, say named "NEWID".

*THEN* do:

Code:

    SELECT COUNT(*) AS howmany FROM projects 
    WHERE projectnumber = xxx
    AND autoid < NEWID

If howmany is 4 or more, then this person is too late. Do

Code:

    DELETE FROM projects WHERE autoid = NEWID

And, of course, tell the person that he/she is too late for that project.

If you like, wrap all that in a stored procedure.

You see how it works? It's guaranteed safe, no matter how many people try to get the same project at the same time.

Because the autoincrement/identity field is *GUARANTEED* to be assigned in true order of addition to the DB, that means that, if you count the number of autoid's for that project that are *LOWER* than the value assigned to the current person, if there are 3 or fewer then this person is okay.

tartuffe · August 22nd, 2010, 10:14 AM

Thanks for your explanation. I'll try both ways.
Can you just tell me how to lock/unlock a sqlserver 2008 table in a stored procedure in order to prevent an insert?
I saw there is a lot of possibilities tablock, tablockx,updlock, rowlock ...
Thanks