Thanks Johan, but i mentioned before that adding auto increment column
doesn't solve the issue & it causes errors in the multi threaded
application.
Multiple clients calls this insert procedure simultaneously, so it fails
the transactions if two or more clients reads the same ID value.
I need to insert row simultaneously with the same C_ID into two tables
( table 1 & table 2).
Please let me know if the below solution is genuine or not, I update my
procedure & create a table that contains only ID.
*Solution 1 *: *Create a PK Table & Lock it when calling the Insert
Procedure.*
create table *pk_table*( c_id bigint unsigned NOT NULL) ENGINE=InnoDB ;
insert into pk_table values (219846925); ( Last ID inserted in
the Table1 & Table 2);
*Modified Insert Procedure :-*
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_table`(url_level
tinyint,table2_size int,master_id bigint,table2_type varchar(50),
page_m_date varchar(70),anchortext varchar(150),url_heading
varchar(250),page_url varchar(500),source mediumtext,content_data
mediumtext,page_category varchar(150),table2_language
varchar(10),url_title varchar(250), referred_url varchar(500), keyword
varchar(500))
begin
declare page_id bigint;
start transaction;
*lock table pk_table write;*
select ifnull(max(c_id)+1,1) into page_id from *pk_table*;
insert ignore into table1
(c_id,link_level,content_size,url_id,content_type,page_modified_date,anchor_text,heading,crawled_page_url,source_code,title,referring_url)
values
(page_id,url_level,table2_size,master_id,table2_type,page_m_date,anchortext,ifnull(url_heading,anchortext),page_url,source,url_title,referred_url);
insert ignore into table2
(c_id,link_level,category,content_language,url_id,heading,crawled_page_url,content,publishing_date,keywords)
values
(page_id,url_level,page_category,table2_language,master_id,ifnull(url_heading,anchortext),page_url,content_data,page_m_date,keyword);
*UPDATE pk_table set c_id=LAST_INSERT_ID(c_id+1);*
commit;
unlock tables;
end
Best Regards.
Arthur Fuller wrote:
I agree with the testicular remedy, but in the case of the iron
codpiece, I can think of another approach which may work for you. It
still uses Select, but reads a one-row table, so it shouldn't hurt
performance much. The table serves no other purpose than storing the
next available PK; call the table NextPK, say. The algorithm might go
like this:
1. Lock the table NextPK.
2. Select its value.
3. Update the column with current value + 1.
4. Unlock the table.
5. Do your inserts.
The lock will be very brief, perhaps brief enough to satisfy your
requirement.
--
Arthur
Cell: 647.710.1314
Only two businesses refer to their clientele as users: drug dealing
and software development.
-- Arthur Fuller
