sample.qs

I have a table in MySQL, where one Java program is pushing data at some random time. On another side, I have front end UI which pulls the data from the same table. During the time of writing, UI application is query get stuck with table lock issue.

To overcome this, what I tried is: 

1. Write to table with suffix _java after creation of table tablename_java with that Java program.

2. Once program is completed, rename the original table to _backup

3. Rename tablename_java to table name

4. Drop tablename_backup in next run of step: 1

Table lock issue still exists. Which is obvious.

Another solution which I thought is:

1. Create a tablename_java

2. Add Column ‘PART’

3. Add part column to UNIQUE INDEX and Primary Key

4. Hash partition on tablename_java over column PART into two partitions 0 and 1

5. Create a view on partitioned table (select from tablename_java where active partition)

6. This will be switching active partition flag and UI and Java program will write to alternate partition

When UI is reading from the view, it is taking very long time then table.