Re: MySQL and table locking

Hello jackmc-phpdb, On 21-May-00 02:21:43, you wrote: >> > A better solution is this: >> >> >CREATE TABLE counters >> > ( >> > u int unsigned NOT NULL, >> > c int unsigned NOT NULL, >> > UNIQUE(u) >> > ); >> >> > Then, the individual counter could be implmented this way: >> >> >UPDATE counters SET c=c+1 WHERE u=236; >> >SELECT c FROM counters WHERE u=236; >> >> I don't see why this is a better solution. Locking tables is always a >> risky thing to do and should be avoided if possible. Not only you may risk >> to deadlock database connections but you will be also will be slowing them >> down. > It is a better solution than creating a new table with one column for >EVERY user that gets an account on the system, with this number expected >to be in the 10s of thousands. Also, it is better than the situation of >needing multiple counters for each user. AUTO_INCREMENT creates only ONE >'sequence' per table. If I have 10,000 users that need ten counters each, >I would have to create 10,000 tables. This is not a better solution by >a long shot. Oh, I see your point. I was not paying proper attention to what you are willing to do. I was assume you were willing to assign a unique id for each row of the same column. Sorry. > And, as it turns out, it is not necessary. There is an atomic way >(in the manual) to have one table support multiple counters: >mysql_query("UPDATE counters SET c=last_insert_id(c+1) WHERE u = $u"); >$c = mysql_insert_id(); I am not sure if that is indeed an atomic operation. You read and write to a table entry in the same statement. I think you need to check that with MySQL developers or else you might risk to face the lost update problem. Regards, Manuel Lemos Web Programming Components using PHP Classes. Look at: http://phpclasses.UpperDesign.com/?user=mlemos@acm.org -- E-mail: mlemos@acm.org URL: http://www.mlemos.e-na.net/ PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp --