table lock while creating table using select

Question

Using mysql version 5.0.18 I am creating a table TT, Client 1 set autocommit = false; start transaction Create table TT select * from PT;

PT has tow columns pk bigint not null,name varchar(20)

Client 2 set autocommit = false start transaction insert into PT values(123,'text'); While inserting a row in PT , it is waiting for the table Client 1 to commit. I am unable to insert the row. why? Is it possible to insert the row without waiting for Client 1 to commit.

Answer 1

From what I have seen, MySQL locks tables sometimes, even during a select statement. Read this for more info if you are interested. But it seems to say that InnoDB tables use row-level locking, while many other table types use table-level locking. You can see what you have by typing show table status from dbname.

Hope this helps.

UPDATE: Of course, having said that, I went to test my understanding and I was wrong. It locks regardless of whether it is isam or innodb.