•  

Announcement

Announcement Module
Collapse
No announcement yet.

transaction time out or can't start occasional

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • transaction time out or can't start occasional

    #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?
    Tags: None

  • #2
    CPU: Intel(R) Xeon(TM) 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

    Comment

    Working...
    X