how to prevent simultaneous access to a table?

Discussion in 'ASP .Net' started by Luc, Aug 14, 2010.

  1. Luc

    Luc Guest

    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.DetailsViewInsertEventArgs) 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.GetType(),
    "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.DetailsViewInsertedEventArgs) Handles
    DetailsView2.ItemInserted

    Page.ClientScript.RegisterClientScriptBlock(Me.GetType(),
    "myscript", _
    " alert('you.are accepted');" & _
    " window.location.href='start.aspx';", True)
    End Sub
    Luc, Aug 14, 2010
    #1
    1. Advertising

  2. Luc

    Mr. Arnold Guest

    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.
    Mr. Arnold, Aug 14, 2010
    #2
    1. Advertising

  3. 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,

    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/prodinfo/previousversions/books.mspx
    Erland Sommarskog, Aug 14, 2010
    #3
  4. Luc

    Luc Guest

    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" <> 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,
    >
    > 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/prodinfo/previousversions/books.mspx
    >
    Luc, Aug 14, 2010
    #4
  5. 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$...
    > 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" <> 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,
    >>
    >> 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/prodinfo/previousversions/books.mspx
    >>

    >
    >
    Sylvain Lafontaine, Aug 15, 2010
    #5
  6. Luc

    Luc Guest

    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" <> schreef in bericht
    news:%...
    > 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$...
    >> 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" <> 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,
    >>>
    >>> 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/prodinfo/previousversions/books.mspx
    >>>

    >>
    >>

    >
    >
    Luc, Aug 15, 2010
    #6
  7. 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,

    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/prodinfo/previousversions/books.mspx
    Erland Sommarskog, Aug 15, 2010
    #7
  8. 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,

    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/prodinfo/previousversions/books.mspx
    Erland Sommarskog, Aug 15, 2010
    #8
  9. Luc

    Mr. Arnold Guest

    "Luc" <ll@nospam> wrote in message
    news:%23ljoD%23$...
    > 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.
    Mr. Arnold, Aug 15, 2010
    #9
  10. Luc

    3P Guest

    Dnia 15-08-2010 o 13:14:33 Mr. Arnold <MR. <> napisa³(a):

    >
    > "Luc" <ll@nospam> wrote in message
    > news:%23ljoD%23$...
    >> 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.
    3P, Aug 15, 2010
    #10
  11. >
    > 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.
    >


    Isn't it possible to set the transaction isolation level to repeatable
    read, rather than serializable? Would that not preserve the value
    returned by count() until the transaction is finished? Or is it that
    we run into a case of "phantom reads", meaning that someone else can
    insert a record, and that will actually change what count() returns?

    Behzad
    Behzad Sadeghi, Aug 15, 2010
    #11
  12. Luc

    Mr. Arnold Guest

    On 8/15/2010 11:22 AM, 3P wrote:
    > Dnia 15-08-2010 o 13:14:33 Mr. Arnold <MR. <> napisa³(a):
    >
    >>
    >> "Luc" <ll@nospam> wrote in message
    >> news:%23ljoD%23$...
    >>> 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.


    I have nothing to say to you little one nor do I want to be bothered
    with you either. You need to stay out of my sight.
    Mr. Arnold, Aug 15, 2010
    #12
  13. "Behzad Sadeghi" <> wrote in message
    news:...
    > >
    >> 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.
    >>

    >
    > Isn't it possible to set the transaction isolation level to repeatable
    > read, rather than serializable? Would that not preserve the value
    > returned by count() until the transaction is finished? Or is it that
    > we run into a case of "phantom reads", meaning that someone else can
    > insert a record, and that will actually change what count() returns?
    >
    > Behzad


    Quick answer here (I don't have time to make some tests): a repeatable read
    won't block the insertion of a new record. You could try adding an
    exclusive lock in the Select query but if you have no row at all - ie, this
    transaction will be the first one to insert a row - then nothing will be
    blocked.

    However, adding a exclusive lock will probably be a better option than just
    using the serializable level because it will greatly readuce the possibility
    of having a deadlock from some other procedure, at the expense of a reduced
    concurrency; which is probably of no concern in this particular case.

    For the count(), I'm not sure (I will have to test this possibility later)
    but in any case, if it's blocking the insertion of new rows then it is doing
    exactly the same thing as the serializable level and it will put the exact
    same locks; so you'll save nothing.

    --
    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server
    (French)
    Sylvain Lafontaine, Aug 15, 2010
    #13
  14. "3P" <> wrote in message news:eek:p.vhhjnzwumsp0fz@mcs...
    > Dnia 15-08-2010 o 13:14:33 Mr. Arnold <MR. <> napisa³(a):
    >
    >>
    >> "Luc" <ll@nospam> wrote in message
    >> news:%23ljoD%23$...
    >>> 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.


    Yes, this is another possibility. A single query is always seen as a
    transaction by SQL-Server, so it should put the required locks in order to
    make sure that this will go correctly. (Albeit it might be hard to design a
    test to be sure that this is what SQL-Server will effectively do.).

    Also, in this kind of situation, you often have to make some other checks
    beside just checking the plain number of inscriptions: the success or
    failure of a previous course, the number of accumulated credits, the total
    number of inscriptions from this student for other courses or sports, etc.,
    etc., etc.

    You also must be careful about the possibility of deadlock: if you have
    multiple indexes and if you can't control the order in which they are
    accessed and locked, it's pretty easy to get into a deadlock situation.

    Personally, I prefer to put a serie of Select statements with some exclusive
    locks at the beginning of every procedure and in the same order for
    accessing the various tables. This way, I can greatly minimize the risk of
    getting a deadlock but of course, at the expense of diminished concurrency.)

    --
    Sylvain Lafontaine, ing.
    MVP - Access
    Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
    Independent consultant and remote programming for Access and SQL-Server
    (French)
    Sylvain Lafontaine, Aug 15, 2010
    #14
  15. Sylvain Lafontaine () writes:
    > "3P" <> wrote in message news:eek:p.vhhjnzwumsp0fz@mcs...
    >> Couldn't You just add where clause to that insert that counts students?
    >> Then the fifth insert wouldn't succeed. That's optimistic concurrency.

    >
    > Yes, this is another possibility. A single query is always seen as a
    > transaction by SQL-Server, so it should put the required locks in order
    > to make sure that this will go correctly. (Albeit it might be hard to
    > design a test to be sure that this is what SQL-Server will effectively
    > do.).


    Note that with the default isolation level, READ COMMITTED, this would not
    be safe to prevent two students both signing up for that last place in the
    project. You would need SERIALIZABLE for that.

    Behzad Sadeghi suggested that REPEATABLE READ would do, and yes it would
    if there is a column Project.NoOfSignedUp. Then you can read this value,
    and then it can't be updated. In fact, if you update the column first
    thing, READ COMMITTED would be enough. Note, however, that such a column
    adds redundancy to the database.

    There is one more solution, which probably is the best if locking is
    absolutely desireable, and that is to use an application lock on the
    project. The reason that this is better than SERIALIZABLE is that the
    latter is very prone to deadlocks.

    However, it is quite clear thta Luc is a beginner in SQL, probably
    working with some training example. I don't think this is the right
    occasion for him to learn working with application locks. Or bother
    too much about locking at all. I still think the trigger check is the
    best solution. And the trigger is needed anyway.

    --
    Erland Sommarskog, SQL Server MVP,

    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/prodinfo/previousversions/books.mspx
    Erland Sommarskog, Aug 15, 2010
    #15
  16. Luc

    Mr. Arnold Guest

    On 8/15/2010 2:48 PM, Sylvain Lafontaine wrote:
    > "3P"<> wrote in message news:eek:p.vhhjnzwumsp0fz@mcs...
    >> Dnia 15-08-2010 o 13:14:33 Mr. Arnold<MR.<> napisa³(a):
    >>
    >>>
    >>> "Luc"<ll@nospam> wrote in message
    >>> news:%23ljoD%23$...
    >>>> 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.

    >
    > Yes, this is another possibility. A single query is always seen as a
    > transaction by SQL-Server, so it should put the required locks in order to
    > make sure that this will go correctly. (Albeit it might be hard to design a
    > test to be sure that this is what SQL-Server will effectively do.).
    >


    What I would do here is use an ASP.NET UI/Model View
    Presenter/BLL/ASP.NET WCF Web service/DAL logical layer approach with
    all layers on the same machine and the Web service too.

    I would set the WCF Web service 'concurrent connection throttle' to 1 so
    that only one user could use the service at a time concurrently,
    blocking other users until the connection was closed, a singleton
    approach.

    With the DAL behind the WCF Web service with the throttle setting at 1,
    there is no way the DAL could be accessed simultaneously by more than
    one user through the WCF Web service.

    The methods in the BLL to open a connection and call a WCF Web service
    method would set in a loop until they could use the connection or stop
    after so many attempts. But I don't see this being a problem, as things
    would not be happening so much in this situation for the OP that there
    would be a service lockout.

    The BLL connection to the WCF service is opened, it calls a method on
    the WCF Web service interface that in turn calls a method on the DAL to
    return the count to the BLL. If count is OK, then BLL inserts the record
    through WCF/DAL, closes the connection and reports back that it was
    successful, otherwise, close the connection and report back everything
    was not successful. On a so many attempts loop stoppage, to inform the
    user to try again.

    Either way, only one user at a time could do anything and not
    simultaneously, which would be easily testable using a test harness like
    MBunit and a functional test.
    Mr. Arnold, Aug 15, 2010
    #16
  17. Luc

    3P Guest

    Dnia 15-08-2010 o 23:21:30 Erland Sommarskog <>
    napisa³(a):

    > Sylvain Lafontaine () writes:
    >> "3P" <> wrote in message news:eek:p.vhhjnzwumsp0fz@mcs...
    >>> Couldn't You just add where clause to that insert that counts students?
    >>> Then the fifth insert wouldn't succeed. That's optimistic concurrency.

    >>
    >> Yes, this is another possibility. A single query is always seen as a
    >> transaction by SQL-Server, so it should put the required locks in order
    >> to make sure that this will go correctly. (Albeit it might be hard to
    >> design a test to be sure that this is what SQL-Server will effectively
    >> do.).

    >
    > Note that with the default isolation level, READ COMMITTED, this would
    > not
    > be safe to prevent two students both signing up for that last place in
    > the
    > project. You would need SERIALIZABLE for that.
    >
    > Behzad Sadeghi suggested that REPEATABLE READ would do, and yes it would
    > if there is a column Project.NoOfSignedUp. Then you can read this value,
    > and then it can't be updated. In fact, if you update the column first
    > thing, READ COMMITTED would be enough. Note, however, that such a column
    > adds redundancy to the database.
    >


    I don't understand it.

    INSERT INTO STUDENTS VALUES(...) WHERE StudentsCount < 4

    should work always right?

    That's how ADO.NET optimistic concurrency works (using timestamp columns).
    3P, Aug 17, 2010
    #17
  18. Luc

    3P Guest

    Dnia 15-08-2010 o 23:48:18 Mr. Arnold <> napisał(a):

    >
    > On 8/15/2010 2:48 PM, Sylvain Lafontaine wrote:
    >> "3P"<> wrote in message news:eek:p.vhhjnzwumsp0fz@mcs...
    >>> Dnia 15-08-2010 o 13:14:33 Mr. Arnold<MR.<>
    >>> napisa³(a):
    >>>
    >>>>
    >>>> "Luc"<ll@nospam> wrote in message
    >>>> news:%23ljoD%23$...
    >>>>> 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.

    >>
    >> Yes, this is another possibility. A single query is always seen as a
    >> transaction by SQL-Server, so it should put the required locks in order
    >> to
    >> make sure that this will go correctly. (Albeit it might be hard to
    >> design a
    >> test to be sure that this is what SQL-Server will effectively do.).
    >>

    >
    > What I would do here is use an ASP.NET UI/Model View
    > Presenter/BLL/ASP.NET WCF Web service/DAL logical layer approach with
    > all layers on the same machine and the Web service too.
    >

    I think You should add some more layers. Maybe You should implement sth in
    assembler
    and then make C wrapper for it and call it with PInvoke.

    Give a small boy a hammer, and he will find that everything he encounters
    needs pounding.
    (http://en.wiktionary.org/wiki/if_all_you_have_is_a_hammer,_everything_looks_like_a_nail)
    3P, Aug 17, 2010
    #18
  19. 3P () writes:
    > I don't understand it.
    >
    > INSERT INTO STUDENTS VALUES(...) WHERE StudentsCount < 4
    >
    > should work always right?
    >
    > That's how ADO.NET optimistic concurrency works (using timestamp columns).


    It's difficult to say what you don't understand, since the syntax you
    suggest is not correct.

    But if you have in mind

    INSERT STUDENTS (...)
    SELECT ...
    WHERE (SELECT COUNT(*) FROM projects WHERE projid = @projid) < 4

    You need serializable isolation for it to be safe. READ COMMITTED or
    REPEATABEL READ will not do.

    --
    Erland Sommarskog, SQL Server MVP,

    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/prodinfo/previousversions/books.mspx
    Erland Sommarskog, Aug 17, 2010
    #19
  20. Luc

    Luc Guest

    thanks to all.
    i solved this by adding a varchar field in table 'student' and promoting it
    in combinaison with a unique identifier to primary key.
    In that field, this value is inserted: CONVERT(varchar(20), GETDATE(), 113))

    When two students try at the same time to subscribe, there must be at least
    one second of difference between both students. I did this because when
    putting the value getdate() in a DateTime field, two students will normaly
    never have exactly the same milli-second.

    Suppose now there are 5 milli-second between both students, then both
    students may be accepted because 5 milli-second may be not enough for the
    latest query to calculate the new count() and to conclude the latest student
    must be refused, while there is no violation of the primary-key restriction.



    "Brian Cryer" <not.here@localhost> schreef in bericht
    news:...
    > "Luc" <ll@nospam> wrote in message
    > news:...
    >> 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?

    >
    > Possible options:
    >
    > 1. Surround your code in a mutex. That way even if two people click the
    > save-button at exactly the same time, only one them will get the mutex and
    > the other will block until it can get the mutex. Thus your code only runs
    > once. Good solution for a single server, probably wouldn't work in a
    > web-farm.
    >
    > 2. Check before AND after. If you have exceeded your maximum afterwards
    > then roll it back (or delete the entry). This doesn't avoid the race
    > condition, just puts it back. You can go one step further than this, in
    > that if each entry in the table has an auto-increment value then you could
    > work out which was the later one to be added and only roll that one back.
    >
    > 3. A solution based around either a database trigger to prevent/react to
    > too many record in a table.
    >
    > I suspect the mutex solution is probably the easiest one.
    >
    > Hope this helps.
    > --
    > Brian Cryer
    > http://www.cryer.co.uk/brian
    >
    Luc, Aug 18, 2010
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. George Ter-Saakov
    Replies:
    0
    Views:
    953
    George Ter-Saakov
    Jun 24, 2003
  2. COHENMARVIN
    Replies:
    1
    Views:
    2,132
    Kevin Spencer
    Sep 26, 2005
  3. mamabe
    Replies:
    0
    Views:
    345
    mamabe
    Mar 18, 2005
  4. Pierre Alexis
    Replies:
    1
    Views:
    410
    Jeff Schwab
    Mar 4, 2004
  5. andrew maddox

    Simultaneous DESKeyFactory access issue

    andrew maddox, Nov 20, 2006, in forum: Java
    Replies:
    2
    Views:
    438
Loading...

Share This Page