Re: MySQL and table locking

-----BEGIN PGP SIGNED MESSAGE----- Big Brother tells me that Manuel Lemos said: > Hello Jack, > > On 19-May-00 12:30:20, 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. 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(); - -- Jack McKinney Whoever put the '.' next to the '/' The Lorentz Group on a keyboard obviously never used http://www.lorentz.com the -R option to /bin/chown F4 A0 65 67 58 77 AF 9B FC B3 C5 6B 55 36 94 A6 -----BEGIN PGP SIGNATURE----- Version: 2.6.2 iQCVAwUBOSdkU0Zx0BGJTwrZAQHnEQP9Ecef9gHI5TdzyLVcww9jr1qe69xx1Cn4 vAUPXISOZNVd7m9FhhWlrthMa3ul5sONbvdiQqqfK3CHNKiXDOMns0Re/bEG4cwF v41ikMz64ak6t16VkKkICM8n2EBOhiHJL7osSk1xfTRXK6LisdDTKk/pdC7VJ6hR U4Bjnez9ONI= =UjZ6 -----END PGP SIGNATURE-----