Hibernate: table lock with a select?

Question

I have this method that selects a row from table product_customer:

public static String getQuoziente(String pcCustomer, String pcAccountNumber, String prodCode) {
    String quoziente=null;
    Session session = HibernateUtil.getSessionFactory().openSession();

    try {
        String sql = String.format("SELECT pc_quoziente FROM product_customer " +
                "WHERE pc_customer=:cliente and pc_account_number=:numacc  and pc_prod_desc=:prod");
        quoziente = (String) session.createSQLQuery(sql)
                .setParameter("cliente", pcCustomer)
                .setParameter("numacc", pcAccountNumber)
                .setParameter("prod", prodCode)
                .uniqueResult();
    } finally {
        session.close();
    }
    return quoziente;
}

After this query try to truncate the table from MySqlWorkbench with:

TRUNCATE wla.product_customer; 

But truncate is blocked from a lock and never ends. Why should a simple select row lock the table?

PS: i can delete rows with DELETE, only truncate is locked

Answer 1

You would need to commit the transaction in the code before you run a TRUNCATE statement. MySQL maintains metadata locks, and if there is a lock from a DML statement, it needs to be released through a commit before executing a DDL statement (TRUNCATE in this case). SELECT and DELETE are DML statements and TRUNCATE is a DDL statement. So, you'll be able to execute DELETE but not TRUNCATE unless lock is release.