Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    3

    Unanswered: Mysql master table gets locked during insert into temporary table

    Guys,

    I have a mysql table(tblrates) which continuously receives insert statements to insert rows into that table.

    I have a stored procedure which selects rows from that table fora given criteria and insert into a temp table t process them(say temprates).

    This procedure is called from an application. Whenever the application executes the stored procedure the master table(tblrates) gets locked and the lock gets released only after the insert into the temporary table is complete. Because of this the mastre table was not able to catch up with the data and it always lags during the peak hours.

    I need both the insert into master table(tbltrates) and procedure to select into the temp table to work simultaneously without locking.Is there a way to avoid this?

    Thanks,

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I'm not too familiar with MySQL yet, but is there an uncommitted read?What are you doing with the data in the temp table? I've always thought temp tables were one of the worst items ever introduced to DBMS's, as most times they are used as the lazy person's way of doing the work and not the original intent.

  3. #3
    Join Date
    Mar 2009
    Posts
    3
    The reason for using temp table is that we process those rows and do some calculations for those rows and return those rows as a resultset to the applications.Since there are many claculations based on those rows, i dont want to read those rows everytime from the mastre table.Thats why we store them in temporary table.

  4. #4
    Join Date
    Mar 2009
    Posts
    3
    Also i checked my 'TRANSACTION ISOLATION LEVEL' and it shows as 'REPEATABLE READ'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •