List:General Discussion« Previous MessageNext Message »
From:Adarsh Sharma Date:February 23 2012 4:56am
Subject:Re: Removing Data Duplicacy
View as plain text  
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
>
>
>


Thread
Removing Data DuplicacyAdarsh Sharma22 Feb
  • Re: Removing Data DuplicacyJohan De Meersman22 Feb
    • Re: Removing Data DuplicacyArthur Fuller22 Feb
      • Re: Removing Data DuplicacyJohnny Withers22 Feb
      • Re: Removing Data DuplicacyAdarsh Sharma23 Feb
        • Re: Removing Data DuplicacyJohan De Meersman23 Feb
Re: Removing Data DuplicacyJohnny Withers22 Feb
  • Re: Removing Data DuplicacyJohan De Meersman22 Feb