Hello. > The table should not be locked, right :D
Most probably it will be locked. If you are not running in strict SQL mode, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that does not name all the columns in the table, unnamed columns are set to their default values. For an AUTO_INCREMENT column, the default value is the next value in the sequence. So your insert will put an AUTO-INC lock on the table. Ady Wicaksono wrote: > Hi Gleb > > Thanx a lot for explanation, make sense. > I haven't try with INSERT INTO Y (t_y_time,....and so on) SELECT > (t_x_time, ... and so on) FROM X > excluding t_y_id > > The table should not be locked, right :D > > Thx > > > Gleb Paharenko wrote: > >> Hello. >> >> It seems that the problem is in the t_y_id auto_increment field. InnoDB >> puts special AUTO-INC table lock, and prevent other threads from >> inserting into Y. See: >> http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-column.html >> >> >> >> Ady Wicaksono wrote: >> >> >>> Below is the SQL to create table Y >>> >>> CREATE TABLE `Y` ( >>> `t_y_id` int(11) NOT NULL auto_increment, >>> `t_y_time` timestamp NOT NULL default CURRENT_TIMESTAMP, >>> `t_y_dest` varchar(16) NOT NULL default '', >>> `t_y_msg` varchar(160) NOT NULL default '', >>> `t_y_status` tinyint(2) NOT NULL default '0', >>> `t_y_type` varchar(16) NOT NULL default '', >>> `t_y_trx_id` varchar(40) NOT NULL default '', >>> `t_y_trx_date` varchar(33) NOT NULL default '', >>> `t_y_serviceid` varchar(20) NOT NULL default '', >>> `t_y_pin` varchar(15) NOT NULL default '', >>> `t_y_key` varchar(20) NOT NULL default '', >>> `t_y_ans` varchar(160) NOT NULL default '', >>> `in_sms_message_id` varchar(22) NOT NULL default '', >>> `in_sms_time` datetime NOT NULL default '0000-00-00 00:00:00', >>> `t_y_city` varchar(50) NOT NULL default '', >>> PRIMARY KEY (`t_y_id`), >>> KEY `t_idx01` (`t_y_type`(3)), >>> KEY `t_idx02` (`t_y_key`(3)), >>> KEY `t_idx03` (`t_y_ans`(8)), >>> KEY `t_idx04` (`t_y_dest`(7)), >>> KEY `t_idx05` (`t_y_dest`(13),`t_y_key`(15),`t_y_ans`(10)), >>> KEY `t_idx06` (`t_y_time`), >>> KEY `t_idx07` (`t_y_time`,`t_y_key`(6)), >>> KEY `t_idx08` (`t_y_trx_id`(8)), >>> KEY `t_idx09` (`t_y_trx_id`(10),`t_y_dest`(6)), >>> KEY `t_idx10` (`t_y_status`,`t_y_type`(3)), >>> KEY `t_idx11` (`in_sms_time`), >>> KEY `t_idx12` (`t_y_time`,`t_y_type`(3)), >>> KEY `t_idx13` (`t_y_city`(7)) >>> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 >>> >>> >> >> >> >> >> >> > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
