表锁读写别名

Question

我正在尝试让它工作，但得到一个错误的t_write was not locked.

我正在从最高值的sn + 1更新col make和col sn

LOCK TABLES 1_makes AS t_read READ, 1_makes as t_write WRITE;

UPDATE t_write 
SET 
    make = 'Pioneer',
    sn = (SELECT 
            *
        from
            (SELECT 
                MAX(sn)
            FROM
                t_read
            WHERE
                id <> 0) as new_sn) + 1
WHERE
    id = 2;

UNLOCK TABLES;

Answer 1

这样如何：

LOCK TABLES 1_makes WRITE, 1_makes AS t_read READ;

UPDATE 1_makes
SET 
    make = 'Pioneer',
    sn = (SELECT 
            *
        from
            (SELECT 
                MAX(sn)
            FROM
                1_makes AS t_read
            WHERE
                id <> 0) as new_sn) + 1
WHERE
    id = 2;

UNLOCK TABLES;