is it good to increment a value automatically

[Jefhandle]

Hello,
Some people say to define a value (colume) as auto increment to increase the value in database is not good
because the database should operate it f.i increase the value userID in the bad way,where database do it automatically:
insert(x,y,z,.......)

They recommand to increase it by yourself
f.i. :
uID=getLastUserId()+1;
insert (uID,x,y,z,...)
what is better??
thanks

 

[r937]

let the database do it

if you do this yourself --

uID=getLastUserId()+1;

then you also need to lock the table before doing this (and don't release the lock until after the INSERT)

locks are a performance killer

r937.com | rudy.ca

 

[sleipnir214]

I completely agree with r937. Unless you are very careful, you will have issues with concurrent users trying to perform the increment at the same time.

Let the database engine do it. It's already multiuser safe.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[Jefhandle]

let say, I have a web site, which is visited at the same time by 1000 people, what is in this case? Is it too much or
still for the database engine a penny!!?

 

[r937]

more like a ha'penny

r937.com | rudy.ca

 

[sleipnir214]

I doubt it's a ha'penny or a decipenny or a centipenny [and if those aren't words, they should be].

Especially compared to the performance hit you're going to take in the locking and unlocking of data by an extra-database-server entity (your code) to enforce multi-user safeness.

Use auto_increment. As Martha Stewart would say, "It's a good thing."



Want the best answers? Ask the best questions! TANSTAAFL!

 

[Jefhandle]

are you talking about mysql only or generall? because i'm using MS Sql with the MS Server. Althougth the main features should be the same.

 

[sleipnir214]

In general.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[sleipnir214]

Sorry. Hit the wrong button.

The advice is general, although I gave a MySQL-specific example.




Want the best answers? Ask the best questions! TANSTAAFL!