Re: Testing Concurrent Insert on InnoDB

[Ady Wicaksono]

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

   




 




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]