MySQL and table locking

-----BEGIN PGP SIGNED MESSAGE----- Consider: CREATE TABLE counter ( c int unsigned NOT NULL AUTO_INCREMENT, UNIQUE(c) ); This allows me to get a new unique integer whenever I need it by inserting a 0 into this table, and then getting mysql_insert_id(). Usually you would have more data in the table, but not always. For example, the above could be used to put a counter on a web page. I have a site whereby each person that registers and is given an account gets a unique account number by the above method. My current problem is that each of these users needs a unique number associated with them (for example, suppose I wanted a counter that displays how many times this particular user has logged in). One solution is create a new table like the above for every users that logs in. For example, user #236: CREATE TABLE counter_236 ( c int unsigned NOT NULL AUTO_INCREMENT, UNIQUE(c) ); 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; But, this is a race condition. This brings me to my basic question: I am running linux+apache+php. A given thread of the apache server might run a couple of dozen PHP scripts before it exits. I am using mysql_pconnect() instead of mysql_connect(), which means that the mysql connection is kept open even after a script finishes, so that the next script can re-use the connection instead of having to reconnect. If I use MySQL's LOCK TABLES, and the PHP script exits without executing the UNLOCK TABLES command (e.g., it runs out of execution time, or perhaps there is a coding error and the unlock is never reached), will the tables still be locked, thus possibly deadlocking the server? Alternately, is there a way to accomplish the same thing without table locking? The actual application I am using cannot afford the error that would be caused by the race condition (two different threads working with the same number, thinking that the number is unique to them). - -- 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 iQCVAwUBOSVP40Zx0BGJTwrZAQFBNQP+IEhvTGPmE7R6+teR+uCGnMXsTuxU0F4B 57IgP5H2qcntqXHB4HvhGxU+yw5TQ8YwWvzUfhuyyDT+XzPzUQSEdEL8kFz2iH6s Px0rnVV3EQ0COCTN+8m4opLS8+Phj3n5469OpjCLmpswHDHWoIa1F+86bhgsryYH NS4w7qSPwQ4= =T3X1 -----END PGP SIGNATURE-----