how to prevent simultaneous access to a table?

[Erland Sommarskog]

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

 

[Gene Wirchenko]

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

 

[Luc]

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

 

[Mr. Arnold]

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_you_have_is_a_hammer,_everything_looks_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