MySQL8 单表类似转账功能，避免死锁 update SQL

        with t as (select uid,
        case when uid = #{obtainId} then coin_total + #{gold} else coin_total end coin,
        case when uid = #{uid} then diamond_total + #{diamond} else diamond_total end diamond
        from user_species where uid in (#{obtainId}, #{uid}) for update)
        update user_species join t on t.uid = user_species.uid
        set user_species.coin_total = t.coin, user_species.diamond_total = t.diamond
        where user_species.uid in (#{obtainId}, #{uid}) and (select count(*)
            from t where coin >= 0 and diamond >= 0) = 2

 避免死锁的方式：一条sql中同时锁住两个用户。