如何使用数据库锁机制解决资源争用问题

DROP FUNCTION IF EXISTS myf1;
CREATE FUNCTION myf1() RETURNS INT
BEGIN

    DECLARE p INT DEFAULT 0;
    DECLARE c INT DEFAULT 0;#定义局部变量
    DECLARE d INT DEFAULT 0;#定义局部变量
    while c = 0 do
    		select min(id) into p from all_port where status = '0';
    		update all_port set status = '1' where id = p and status = '0';
    		set c = row_count();
    		
    		select sleep(1) into d;
    end while;
    
    RETURN p;
END;


select myf1();
select * from all_port;