Lock troubles.

[Michael Widenius]

Hi!

>>>>> "Peter" == Peter Zaitsev <pz@stripped> writes:

Peter> Somthere in version 3.23 I started to get the following trouble: The query
Peter> is waiting for table to be unlocked therefore there are no queryes which did
Peter> lock it:

Peter> Queries list:

Peter> pz@samson:/spylog/var > mysqladmin processlist | grep -v Sleep
Peter> +-------+-------+---------------------+-------------+---------+------+------
Peter> --------+-------------------------------------------------------------------
Peter> -----------------------------------+
Peter> | Id    | User  | Host                | db          | Command | Time | State
Peter> | Info
Peter> |
Peter> +-------+-------+---------------------+-------------+---------+------+------
Peter> --------+-------------------------------------------------------------------
Peter> -----------------------------------+
Peter> | 21    | titan | station36.amt.ru    | counter     | Query   | 138  |
Peter> Locked       | UPDATE counter_info SET hits=5093, hosts=2159, visitors=2486,
Peter> voters=0, jumps=6, loads=6946, comming |
Peter> | 29631 | pz    | localhost           | rating      | Query   | 1702 |
Peter> Locked       | alter table ct00l02Secondrsites add column IS_IN_RATING
Peter> tinyint unsigned not null                    |
Peter> | 29642 | nscon |                     | counter     | Query   | 71   |
Peter> Locked       | update counter_info set fp_type=0,op_type=0, fp_res=2,
Peter> op_res=2, fp_number=10,op_number=10 where cou |
Peter> | 29679 | root  | localhost           | st00        | Query   | 130  |
Peter> Sending data | INSERT INTO layers_st00.g00stat_enterpages SELECT * FROM
Peter> layers.stat_enterpages WHERE layer_id IN (6 |
Peter> | 29731 | pz    | localhost           |             | Query   | 0    |
Peter> | show processlist
Peter> |
Peter> +-------+-------+---------------------+-------------+---------+------+------
Peter> --------+-------------------------------------------------------------------
Peter> -----------------------------------+


Peter> As You see there is no queries who locked table ct00l02Secondrsites but
Peter> still alter query cannot start.  I tried to run mysqladmin debug - it says
Peter> that there is no locked tables by lock table but still the problem presists.
Peter> Also note the zero number of used keyblocks which is wrong....

Peter> Before this problem started one of the programs which locked table
Peter> ct00l02Secondrsites and then the client program was abnormaly terminated -
Peter> The connection probebly still not closed but I can't find it as there is no
Peter> any tool for that.

Peter> I'm not shure this is the problem as if the thread is not terminated the
Peter> lock should still exist and reported by mysqladmin debug.

mysqladmin should return all locks;  I don't know of any reason why it
shouldn't!

Try starting mysql with --skip-locking;  If this happens on Solaris,
the problem could be a bug in the lockd manager;  --skip-locking will
avoid this problem.

Peter> Is there any way I can avoid such problems or to detect and kill hanged
Peter> connection ?

Try applying the included patch (We will put this in 3.23.12); If this
happens again 'mysqladmin proc' may be able to give us more exact
information of what happens!

*** /my/monty/master/mysql-3.23.11-alpha/sql/lock.cc	Sat Nov 27 12:22:42 1999
--- ./lock.cc	Fri Feb 25 19:05:07 2000
***************
*** 32,38 ****
  MYSQL_LOCK *mysql_lock_tables(THD *thd,TABLE **tables,uint count)
  {
    MYSQL_LOCK *sql_lock;
-   thd->locked=1;
    for (;;)
    {
      if (!(sql_lock = get_lock_data(tables,count)))
--- 32,37 ----
***************
*** 47,67 ****
        break;
      }
      thd->proc_info=0;
      if (thr_multi_lock(sql_lock->locks,sql_lock->lock_count))
      {
        thd->some_tables_deleted=1;		// Try again
        sql_lock->lock_count=0;			// Locks are alread freed
      }
      else if (!thd->some_tables_deleted)
        break;
!     /* some table was altered or deleted. reopen tables marked deleted */
  
      mysql_unlock_tables(sql_lock);
      sql_lock=0;
      if (wait_for_tables(thd))
        break;					// Couldn't open tables
    }
-   thd->locked=0;
    if (thd->killed)
    {
      my_error(ER_SERVER_SHUTDOWN,MYF(0));
--- 46,75 ----
        break;
      }
      thd->proc_info=0;
+     thd->locked=1;
      if (thr_multi_lock(sql_lock->locks,sql_lock->lock_count))
      {
        thd->some_tables_deleted=1;		// Try again
        sql_lock->lock_count=0;			// Locks are alread freed
      }
      else if (!thd->some_tables_deleted)
+     {
+       thd->locked=0;
        break;
!     }
  
+     /* some table was altered or deleted. reopen tables marked deleted */
      mysql_unlock_tables(sql_lock);
+     thd->locked=0;
      sql_lock=0;
+     thd->proc_info="Waiting for tables";
      if (wait_for_tables(thd))
+     {
+       thd->proc_info=0;
        break;					// Couldn't open tables
+     }
+     thd->proc_info=0;
    }
    if (thd->killed)
    {
      my_error(ER_SERVER_SHUTDOWN,MYF(0));


Regards,
Monty