Monty BTW, it's a thrill to hear from the CTO.
The procedure for locking a table is more complex but will attempt to describe it further for you. Any of the 150 daemons can insert to the hash tables. The data in the row can be used by all 150 daemons. The primary key in hash table is the "id" field which is an auto-increment type. This "id" field is the hash value inserted in the main table as foreign key. When a daemon starts up, it reads the hash table with each entry a key-value pair. The value is the "id" field. During the course of logging info to the main table, it may find an entry that is not in it's local hash in RAM. When this occurs, it does a sql select on the hash. If a row is returned, it will insert the key-value pair into the it's hash and insert an entry into the main table. If no row is returned, then the following happens: 1) lock table ... 2) select ... 3) if no row is returned, 4) insert ... 5) use LAST_INSERT_ID() to get the value of the key 6) else get the key-value pair 7) unlock table 8) put key-value pair in RAM hash 9) insert row in main table (using new key-value pair) Since >99% of the time, the data hash table information is already stored in the daemon hash table on initial start up. We wanted to lock the table so two different daemons would not enter the same information in the hash tables. Extremely unlikely, but the boss is paranoid. Or is it more experienced. Hope that clears the insertion process on the hash tables. I'm glad that if a connection is lost in steps 2-6, mysql would automatically unlock the tables. That will satisfy the db specification nicely. The architecture for our db came from the optimization chapter in the manual. Thanks! David -----Original Message----- From: Michael Widenius [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 23, 2002 12:59 AM To: Lopez David E-r9374c Cc: Mysql List (E-mail) Subject: table lock - which connection id own's it Hi! >>>>> "Lopez" == Lopez David <E-r9374c <[EMAIL PROTECTED]>> writes: Lopez> AntiSpam - mysql, sql, query Lopez> Version: 3.23.49a, MyISAM, NT, Solaris Lopez> My app is 150 daemons writing logs to mysql. The main Lopez> table is of fixed size but depends on four other tables Lopez> for foreign keys (hash tables). These tables are uploaded Lopez> once to the daemon at start-up. Occasionally, a new entry Lopez> must be written into these hash tables. The procedure is Lopez> to lock the table, insert entry, get the key (auto-increment Lopez> field) and release the lock. A better solution is to use LAST_INSERT_ID() and not use any locks at all. Lopez> But what if the connection dies during the update process. Lopez> If this happens, how can I tell which connection id has the Lopez> lock so I can kill it? If a connection dies, the server will automaticly delete all temporary tables and all table locks. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Michael Widenius <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland <___/ www.mysql.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
