how to prevent simultaneous access to a table?

[Luc]

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

 

[Mr. Arnold]

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.

 

[Erland Sommarskog]

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 address 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/prodinfo/previousversions/books.mspx

 

[Luc]

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

 

[Sylvain Lafontaine]

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]

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?

 

[Erland Sommarskog]

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 address 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/prodinfo/previousversions/books.mspx

 

[Erland Sommarskog]

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 address 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/prodinfo/previousversions/books.mspx

 

[Mr. Arnold]

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.

 

[3P]

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.

 

[Behzad Sadeghi]

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

 

[Mr. Arnold]

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.

 

[Sylvain Lafontaine]

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]

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)

 

[Erland Sommarskog]

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, (e-mail address 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/prodinfo/previousversions/books.mspx

 

[Mr. Arnold]

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.

 

[3P]

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]

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)

 

[Erland Sommarskog]

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, (e-mail address 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/prodinfo/previousversions/books.mspx

 

[Luc]

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.