Mysql locking question

Hi Guys,

I'm using mysql 5.0.42 with InnoDB tables and I'm looking for the best locking methods....

I have a simple table like this:

CREATE TABLE `tblaccess` (
  `accessid` int(11) NOT NULL auto_increment,
  `stateid` int(11) default 0,
  PRIMARY KEY  (`accessid`),
  KEY `tblaccess_stateid_idx` (`stateid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have about 1.000.000 rows in the table.
There are about 30 clients which connects to the databease and issue the same query and select a simple row from the table, works with tha data, and after some minutes, it request another row to work with...

Initially the stateid of all rows are 0 = unprocessed, the processed rows should have a stateid=1 means processed.

the query could look like this:

CREATE DEFINER = 'root'@'localhost' PROCEDURE `sp_getaccessid`()
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
begin
declare    _accessid int default 0;
start transaction;
      select _accessid into _accessid from tblaccess where stateid=0 order by accessid limit 1 for update;
      if _accessid >0 then # we ha a macthing row
            update tblaccess set stateid=1 where acceesid=_accessid;
      end if;
commit;
select _accessid;
end;

This metchod works but seems to lock the whole table instead of only one row....
And sometimes I got an erro in my client:
Deadlock found when trying to get lock; try restarting transaction

How to perform the same action with better performance and without deadlock???

I would like to select a row from a large table in the client and update is't state to processed...
I have 30 simulteanous clients and one row could be processed by only one client...
In other words every row could be processed by only one client...

How do I have to perform the locking???

CREATE DEFINER = 'root'@'localhost' PROCEDURE `sp_getaccessid`()
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
begin
declare    _accessid int default 0;
start transaction;
	select _accessid into _accessid from tblaccess where stateid=0 order by accessid limit 1 for update;
	if _accessid >0 then # we ha a macthing row
		update tblaccess set stateid=1 where acceesid=_accessid;
	end if;
commit;
select _accessid;
end;

Select allOpen in new window