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?

 
 
Erland Sommarskog
Guest
Posts: n/a
 
      08-18-2010
Luc (ll@nospam) writes:
> 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.


Certainly an unorhtodox solution. You have add a varchar value which does
not really carry any information to the primary key? And when the student
signs up for the next project, you change that value?

Well, you still need the trigger to avoid that a project get overbooked.

There is one another approach though. When the project is created, also
create the slots for the students. When a student signs up, you update the
first free row. Check @@rowcount after the UPDATE, and if it's zero, there
were no free slots. There is no possibility for a double booking here.

--
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
 
 
 
 
Gene Wirchenko
Guest
Posts: n/a
 
      08-18-2010
On Wed, 18 Aug 2010 20:00:54 +0200, "Luc" <ll@nospam> wrote:

>thanks to all.
>i solved this by adding a varchar field in table 'student' and promoting it

^^^^^^
ITYM "used a lot of tape and baling wire on". See below.

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


You have apparently created a race condition. One second may
well be enough is most circumstances, but if your system ever gets
really slow at just the right(!) time, you will get a duplicate.

A race condition is very difficult to find if you do not already
know about it. I suggest that you try something else.

REALLY.

[snip]

Sincerely,

Gene Wirchenko
 
Reply With Quote
 
 
 
 
Luc
Guest
Posts: n/a
 
      08-19-2010
Sorry: correction: the primary key of table 'student' is the combination of
the varchar(20) with the time + the foreign key (id) which is the primary
key of table 'project'.
And no, overbooking is impossible, unless the latest query needs more than 1
second for calculating the actual numbers. But we work in an intranet and if
so, i take then 2 seconds. The only drawback is in the inprobable case that
two independant students want to sign in in the same second.
The whole system is only for preventing 'hackers-students' who want to abuse
the system.
But i will consider your idea. I still enjoy learning more. I only don't
know how and where to code @@rowcount ...in the sql command.
Thanks


"Erland Sommarskog" <(E-Mail Removed)> schreef in bericht
news:Xns9DD8F17203663Yazorman@127.0.0.1...
> Luc (ll@nospam) writes:
>> 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.

>
> Certainly an unorhtodox solution. You have add a varchar value which does
> not really carry any information to the primary key? And when the student
> signs up for the next project, you change that value?
>
> Well, you still need the trigger to avoid that a project get overbooked.
>
> There is one another approach though. When the project is created, also
> create the slots for the students. When a student signs up, you update the
> first free row. Check @@rowcount after the UPDATE, and if it's zero, there
> were no free slots. There is no possibility for a double booking here.
>
> --
> 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-31-2010

"3P" <(E-Mail Removed)> wrote in message newsp.vhla8cc0msp0fz@mcs...
> 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.


I think that you should drop dead and end your worthless life. But that is
just my take as to what you should do.
>
> 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)


Are you stalking now? You got nothing else better to do than to stalk
people? You do know that you mean nothing in the grand scheme of things --
right? All you're about is worthless lip service and another clown on the
loose that starts chasing people. What else is new?

You have no clue as to what you're talking about, and what I am talking
about is very simple to do, but of course, you have no expertise with any of
it, and all you have is a bunch of dumb lip service.

Like the OP I am posting to, you should try to learn something other than
the Mickey Mouse routine with everything needs to be done at the UI, because
you don't know any better. You're a dime a dozen and can't make the $$$$$$$
like me.

You should stop chasing little one, like a little clown.

In the meantime, see if you can learn something and make some serious money.
I doubt that you have the ability. I am at a client's site, in a hotel room
in Columbia, South Carolina, and I am being paid some looooong $$$$$$$$$$$'s
to be here, while you Mickey Mouse it little one.

http://www.dofactory.com/Framework/Framework.aspx

 
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