Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > how to prevent simultaneous access to a table?

Reply
Thread Tools

how to prevent simultaneous access to a table?

 
 
Luc
Guest
Posts: n/a
 
      08-14-2010
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 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 nserted into table 'student'.

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

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



 
Reply With Quote
 
 
 
 
Mr. Arnold
Guest
Posts: n/a
 
      08-14-2010
On 8/14/2010 9:48 AM, Luc wrote:
>
> Is there a way to lock the table or something in order to preventing this?


There is nothing you can do in this situation -- not really. You may
need to rethink how you're doing this.

You might be able to do some kind of manual lock/unlock of the
application by using a flag on a record to lockout all users until the
current user has completed the task before you read for the count, but
it could be messy.
 
Reply With Quote
 
 
 
 
Erland Sommarskog
Guest
Posts: n/a
 
      08-14-2010
Luc (ll@nospam) writes:
> 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 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 nserted into
> table 'student'.
>
> Is there a way to lock the table or something in order to preventing this?


There is, but the cure may be worse than the desease. Taken by the latter,
your idea of locking the table would mean only one student at a time
could browse projects.

I think the best way is to add a trigger on the table that checks
that the number of project members are not exceeded. You would keep
the check in the web page, to give users a "nice" error in most cases.
But the trigger would protect you if those naughty students try you out.

--
Erland Sommarskog, SQL Server MVP, http://www.velocityreviews.com/forums/(E-Mail Removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

 
Reply With Quote
 
Luc
Guest
Posts: n/a
 
      08-14-2010

Thanks both for replying. I'll try locking and triggers.
Can you give me the syntax of locking a table? I found something like
'tablock' but i don't know how to use it. I also read two different locks:
exclusive and share ... The purpose is still to allow a select of any
student, but one insert at the time.
Thanks

"Erland Sommarskog" <(E-Mail Removed)> schreef in bericht
news:Xns9DD4DA097F6BEYazorman@127.0.0.1...
> Luc (ll@nospam) writes:
>> 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 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 nserted into
>> table 'student'.
>>
>> Is there a way to lock the table or something in order to preventing
>> this?

>
> There is, but the cure may be worse than the desease. Taken by the latter,
> your idea of locking the table would mean only one student at a time
> could browse projects.
>
> I think the best way is to add a trigger on the table that checks
> that the number of project members are not exceeded. You would keep
> the check in the web page, to give users a "nice" error in most cases.
> But the trigger would protect you if those naughty students try you out.
>
> --
> Erland Sommarskog, SQL Server MVP, (E-Mail Removed)
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>



 
Reply With Quote
 
Sylvain Lafontaine
Guest
Posts: n/a
 
      08-15-2010
Probably that you don't really want to use a tablock because this will lock
out everyone trying to access this table; even if it's only for reading
something not even related to the current choice of your two (or more)
students.

What you need to do is to first open a transaction, then make a Select
statement with the Serializable option on all records associated with the
'Project A'. (Instead of using the Serializable option in the Select
statement, you could also chaange the isolation level to Serializable before
opening the transaction but by doing so, you will often ending up by locking
way too much stuff than you need). With the proper index, not only all the
current records associated with this 'Project A' will be locked but it will
also be forbidden for anyone else to insert a new record associated with
'Project A' because the Serializable option is in effect. (If you don't
have a proper index, you might end up locking the full table; something that
probably you don't want.)

By having a Count in your Select statement, you can then make the decision
of wether or not insert a new record for the current student or return a
error message saying that sorry, but it's too late.

After that, you close the transaction by committing it (or making a rollback
if there has been an error); therefore releasing all locks.

There is a small example with a transaction and an isolation level of
ReadCommitted at the following article:
http://www.vb-helper.com/howto_net_db_transaction.html

In your case, you need to set the isolation level to Serializable or better,
to set this option directly in the Select statement in order to minimize the
locks that will get applied. Instead of opening the transaction from the
..NET connection, you could also put all this logic into a stored procedure.

Search the web for "Serializable Lock" and you'll find more info on that
important topic.

--
Sylvain Lafontaine, ing.
MVP - Access
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"Luc" <ll@nospam> wrote in message
news:%23ljoD%23$(E-Mail Removed)...
> Thanks both for replying. I'll try locking and triggers.
> Can you give me the syntax of locking a table? I found something like
> 'tablock' but i don't know how to use it. I also read two different locks:
> exclusive and share ... The purpose is still to allow a select of any
> student, but one insert at the time.
> Thanks
>
> "Erland Sommarskog" <(E-Mail Removed)> schreef in bericht
> news:Xns9DD4DA097F6BEYazorman@127.0.0.1...
>> Luc (ll@nospam) writes:
>>> 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 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 nserted into
>>> table 'student'.
>>>
>>> Is there a way to lock the table or something in order to preventing
>>> this?

>>
>> There is, but the cure may be worse than the desease. Taken by the
>> latter,
>> your idea of locking the table would mean only one student at a time
>> could browse projects.
>>
>> I think the best way is to add a trigger on the table that checks
>> that the number of project members are not exceeded. You would keep
>> the check in the web page, to give users a "nice" error in most cases.
>> But the trigger would protect you if those naughty students try you out.
>>
>> --
>> Erland Sommarskog, SQL Server MVP, (E-Mail Removed)
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000:
>> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>>

>
>



 
Reply With Quote
 
Luc
Guest
Posts: n/a
 
      08-15-2010
Thanks.
By the way, i can't find the french dotnet.aspnet group anymore (i speak
french). Is it closed or is it a restriction of my provider?

"Sylvain Lafontaine" <(E-Mail Removed)> schreef in bericht
news:%(E-Mail Removed)...
> Probably that you don't really want to use a tablock because this will
> lock out everyone trying to access this table; even if it's only for
> reading something not even related to the current choice of your two (or
> more) students.
>
> What you need to do is to first open a transaction, then make a Select
> statement with the Serializable option on all records associated with the
> 'Project A'. (Instead of using the Serializable option in the Select
> statement, you could also chaange the isolation level to Serializable
> before opening the transaction but by doing so, you will often ending up
> by locking way too much stuff than you need). With the proper index, not
> only all the current records associated with this 'Project A' will be
> locked but it will also be forbidden for anyone else to insert a new
> record associated with 'Project A' because the Serializable option is in
> effect. (If you don't have a proper index, you might end up locking the
> full table; something that probably you don't want.)
>
> By having a Count in your Select statement, you can then make the decision
> of wether or not insert a new record for the current student or return a
> error message saying that sorry, but it's too late.
>
> After that, you close the transaction by committing it (or making a
> rollback if there has been an error); therefore releasing all locks.
>
> There is a small example with a transaction and an isolation level of
> ReadCommitted at the following article:
> http://www.vb-helper.com/howto_net_db_transaction.html
>
> In your case, you need to set the isolation level to Serializable or
> better, to set this option directly in the Select statement in order to
> minimize the locks that will get applied. Instead of opening the
> transaction from the .NET connection, you could also put all this logic
> into a stored procedure.
>
> Search the web for "Serializable Lock" and you'll find more info on that
> important topic.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Access
> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server
> (French)
>
>
> "Luc" <ll@nospam> wrote in message
> news:%23ljoD%23$(E-Mail Removed)...
>> Thanks both for replying. I'll try locking and triggers.
>> Can you give me the syntax of locking a table? I found something like
>> 'tablock' but i don't know how to use it. I also read two different
>> locks: exclusive and share ... The purpose is still to allow a select of
>> any student, but one insert at the time.
>> Thanks
>>
>> "Erland Sommarskog" <(E-Mail Removed)> schreef in bericht
>> news:Xns9DD4DA097F6BEYazorman@127.0.0.1...
>>> Luc (ll@nospam) writes:
>>>> 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 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 nserted into
>>>> table 'student'.
>>>>
>>>> Is there a way to lock the table or something in order to preventing
>>>> this?
>>>
>>> There is, but the cure may be worse than the desease. Taken by the
>>> latter,
>>> your idea of locking the table would mean only one student at a time
>>> could browse projects.
>>>
>>> I think the best way is to add a trigger on the table that checks
>>> that the number of project members are not exceeded. You would keep
>>> the check in the web page, to give users a "nice" error in most cases.
>>> But the trigger would protect you if those naughty students try you out.
>>>
>>> --
>>> Erland Sommarskog, SQL Server MVP, (E-Mail Removed)
>>>
>>> Links for SQL Server Books Online:
>>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>>> SQL 2000:
>>> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>>>

>>
>>

>
>



 
Reply With Quote
 
Erland Sommarskog
Guest
Posts: n/a
 
      08-15-2010
Luc (ll@nospam) writes:
> Thanks both for replying. I'll try locking and triggers.
> Can you give me the syntax of locking a table?


There is no such syntax. SQL Server locks resources as needed. Yes, you
can control it with hints. In this case you could also use application
locks, but overall, if you didn't get this from my first post: don't
do this. You may be fixing a hypothetical problem, while creating a
real one.

The most commonly used locking scheme in a database is "optimistic locking"
which means that you don't lock anything at all, but that you check when
you update that no one on else has updated the row while the user was
looking at it.

In this case, this is served by the trigger. And the trigger is needed
anyway to preserve database integrity.

Yes, you can do as Sylvain suggested and start a serializable transaction,
run a count and then insert if the count turns out well. But the
serializable isolation level is prone to cause deadlocks. Which maybe
is not an issue in this case, but certainly could be in a real-world
application.

--
Erland Sommarskog, SQL Server MVP, (E-Mail Removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

 
Reply With Quote
 
Erland Sommarskog
Guest
Posts: n/a
 
      08-15-2010
Luc (ll@nospam) writes:
> By the way, i can't find the french dotnet.aspnet group anymore (i speak
> french). Is it closed or is it a restriction of my provider?


Microsoft announced a while back that they will close down their newsgroups,
and some of them has already been closed. In practice, they are closing
down their newsserver, and other newsservers may continue to carry
their groups.

--
Erland Sommarskog, SQL Server MVP, (E-Mail Removed)

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

 
Reply With Quote
 
Mr. Arnold
Guest
Posts: n/a
 
      08-15-2010

"Luc" <ll@nospam> wrote in message
news:%23ljoD%23$(E-Mail Removed)...
> Thanks both for replying. I'll try locking and triggers.
> Can you give me the syntax of locking a table? I found something like
> 'tablock' but i don't know how to use it. I also read two different locks:
> exclusive and share ... The purpose is still to allow a select of any
> student, but one insert at the time.


Myself, I would try to find out how to use some kind shared application or
session variable, since it's the same program used by all, to control the
insert lockout.

 
Reply With Quote
 
3P
Guest
Posts: n/a
 
      08-15-2010
Dnia 15-08-2010 o 13:14:33 Mr. Arnold <MR. <(E-Mail Removed)> napisał(a):

>
> "Luc" <ll@nospam> wrote in message
> news:%23ljoD%23$(E-Mail Removed)...
>> Thanks both for replying. I'll try locking and triggers.
>> Can you give me the syntax of locking a table? I found something like
>> 'tablock' but i don't know how to use it. I also read two different
>> locks: exclusive and share ... The purpose is still to allow a select
>> of any student, but one insert at the time.

>
> Myself, I would try to find out how to use some kind shared application
> or session variable, since it's the same program used by all, to
> control the insert lockout.


Couldn't You just add where clause to that insert that counts students?
Then the fifth insert wouldn't succeed. That's optimistic concurrency.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: How include a large array? Edward A. Falk C Programming 1 04-04-2013 08:07 PM
Simultaneous DESKeyFactory access issue andrew maddox Java 2 11-22-2006 03:18 AM
can 3 simultaneous users with Access database cause trouble? COHENMARVIN ASP .Net 1 09-26-2005 07:29 PM
simultaneous directory access causes AccessControlException? mamabe Java 0 03-18-2005 07:46 PM
Preventing simultaneous access to a file by C++ CGI instances Pierre Alexis C++ 1 03-04-2004 03:37 PM



Advertisments