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?

 
 
Behzad Sadeghi
Guest
Posts: n/a
 
      08-15-2010
>
> 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


 
Reply With Quote
 
 
 
 
Mr. Arnold
Guest
Posts: n/a
 
      08-15-2010
On 8/15/2010 11:22 AM, 3P wrote:
> 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.


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.

 
Reply With Quote
 
 
 
 
Sylvain Lafontaine
Guest
Posts: n/a
 
      08-15-2010
"Behzad Sadeghi" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> >
>> 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)



 
Reply With Quote
 
Sylvain Lafontaine
Guest
Posts: n/a
 
      08-15-2010
"3P" <(E-Mail Removed)> wrote in message newsp.vhhjnzwumsp0fz@mcs...
> 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.


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)


 
Reply With Quote
 
Erland Sommarskog
Guest
Posts: n/a
 
      08-15-2010
Sylvain Lafontaine ((E-Mail Removed)) writes:
> "3P" <(E-Mail Removed)> wrote in message newsp.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, 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
 
Mr. Arnold
Guest
Posts: n/a
 
      08-15-2010

On 8/15/2010 2:48 PM, Sylvain Lafontaine wrote:
> "3P"<(E-Mail Removed)> wrote in message newsp.vhhjnzwumsp0fz@mcs...
>> 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.

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

 
Reply With Quote
 
3P
Guest
Posts: n/a
 
      08-17-2010
Dnia 15-08-2010 o 23:21:30 Erland Sommarskog <(E-Mail Removed)>
napisa│(a):

> Sylvain Lafontaine ((E-Mail Removed)) writes:
>> "3P" <(E-Mail Removed)> wrote in message newsp.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).
 
Reply With Quote
 
3P
Guest
Posts: n/a
 
      08-17-2010
Dnia 15-08-2010 o 23:48:18 Mr. Arnold <(E-Mail Removed)> napisał(a):

>
> On 8/15/2010 2:48 PM, Sylvain Lafontaine wrote:
>> "3P"<(E-Mail Removed)> wrote in message newsp.vhhjnzwumsp0fz@mcs...
>>> 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.

>>
>> 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...ks_like_a_nail)
 
Reply With Quote
 
Erland Sommarskog
Guest
Posts: n/a
 
      08-17-2010
3P ((E-Mail Removed)) 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, (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-18-2010
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:(E-Mail Removed)...
> "Luc" <ll@nospam> wrote in message
> news:(E-Mail Removed)...
>> 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
>



 
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