transaction time out or can't start occasional

MySQL & MariaDB Other MySQL® Questions
1

declare _nextPid int;
declare _nextName varchar(20);
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
select ifnull(next_prop,0) from prop where pid=ipid into _nextPid;
select pname from prop where pid=_nextPid into _nextName;
if (_nextPid>0) then
update world_animal set pid=_nextPid,wname=_nextName,last_growup=now() where aid=iaid;
end if;

world_animal records:73959

aid is the primary key,i suppose with read committed should donnot lock whole table,

occasional,call pa_growup(82342,332) will get message:Lock wait timeout exceeded; try restarting transaction
but call pa_growup(82340,331) is work,

what wrong with my store procedure?

2

CPU: Intel(R) Xeon™ CPU 3.00GHz (2992.52-MHz 686-class CPU)
avail memory = 2091335680 (1994 MB)
my.cnf:
[client]
#password = [your_password]
port = 3306
socket = /tmp/mysql.sock
default-character-set=utf8

[mysqld]
default-character-set=utf8
init_connect=‘SET NAMES utf8’

generic configuration options

port = 3306
socket = /tmp/mysql.sock
back_log = 50

max_connections = 2500
max_connect_errors = 5
table_cache = 2048

max_allowed_packet = 16M

The size of the cache to hold the SQL statements for the binary log

binlog_cache_size = 1M
max_heap_table_size = 32M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 4

query_cache_size = 62M

query_cache_limit = 2M

ft_min_word_len = 4

default_table_type = MYISAM

thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 32M

log_slow_queries
long_query_time = 2

log_long_format

key_buffer_size = 32M

read_buffer_size = 2M

read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 1G

myisam_max_extra_sort_file_size = 1G

myisam_repair_threads = 1

myisam_recover

skip-federated

skip-bdb

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 1180M
innodb_data_file_path = ibdata1:10M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 0

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 8M

innodb_log_file_size = 256M
innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 60
innodb_locks_unsafe_for_binlog=1
[mysqldump]

Do not buffer the whole result set in memory before writing it to

file. Required for dumping very large tables

quick

max_allowed_packet = 16M

[mysql]
no-auto-rehash
[isamchk]
key_buffer = 256M
sort_buffer_size = 64M
read_buffer = 4M
write_buffer = 4M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 64M
read_buffer = 4M
Write_buffer = 4M
[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192