CentOS Question posted in
The official documentation can be found here.

Is there a limit for simultaneous transactions in MariaDB? – CentOS

There is Centos 7 server with Asterisk PBX 11.25.3 and mysql Ver 15.1 Distrib 10.3.18-MariaDB, for Linux (x86_64).

extensions.conf:

[from-main-asterisk]
exten => _9XXXXXXXXXXXX,1,MYSQL(Connect connid localhost root rootpasswd mydatabase)
exten => _9XXXXXXXXXXXX,n,GotoIf($["${connid}" = ""]?error,1)
exten => _9XXXXXXXXXXXX,n(select_sim),MYSQL(Query RESULTID ${connid} set autocommit=0)
exten => _9XXXXXXXXXXXX,n,MYSQL(Query RESULTID ${connid} START TRANSACTION)
exten => _9XXXXXXXXXXXX,n,MYSQL(Query RESULTID ${connid} SELECT sim_name FROM  sim_stat  where status = 'FREE'  order by rand() limit 1 ) 
exten => _9XXXXXXXXXXXX,n(sqlresult),MYSQL(Fetch fetchid ${RESULTID} SIM_NAME)
exten => _9XXXXXXXXXXXX,n,MYSQL(Clear ${RESULTID})
exten => _9XXXXXXXXXXXX,n,MYSQL(Query RESULTID ${connid} UPDATE sim_stat set status = "BUSY" where sim_name = "${SIM_NAME}") 
exten => _9XXXXXXXXXXXX,n,MYSQL(Query RESULTID ${connid} COMMIT)
exten => _9XXXXXXXXXXXX,n,Dial(SIP/gsm-${SIM_NAME}#${EXTEN:1},,g)
exten => _9XXXXXXXXXXXX,n,Hangup

exten => h,1,MYSQL(Query RESULTID ${connid} UPDATE sim_stat set status = "FREE"  where sim_name = "${SIM_NAME}")
exten => h,n,MYSQL(Query RESULTID ${connid} COMMIT)
exten => h,n,MYSQL(Disconnect ${connid})

exten => error,1,NoOp(Database connection error!)
exten => error,n,Hangup

sip.conf:

[from-main-asterisk]
type=friend
host=x.x.x.x
qualify=yes
qualifyfreq=60
canreinvite=no
disallow=all
allow=alaw
context=from-main-asterisk

When there are more than 15-20 simultaneous calls goes from trunk i got message:

WARNING[2239][C-00000389] app_mysql.c: aMYSQL_query: mysql_query failed. Error: Lock wait timeout exceeded; try restarting transaction

Is there a limit for simultaneous transactions in MariaDB?

Tags:

2

Answers


  1. 0
    1. Use func_odbc, it deals with connections correctly
    2. Order by rand() do scan whole table ALWAYS
    3. If your table type myisam, any scan of table lock whole table.
    Login or Signup to reply.
  2. 0

    The number of transactions is limited to about 96K. See https://mariadb.com/kb/en/library/innodb-limitations/

    INDEX(status, sim_name) (in that order) may speed up the lengthy query in your transaction.

    Or, you might be able to avoid the table scan required by ORDER BY rand() by using one of the techniques here: http://mysql.rjweb.org/doc.php/random

    Another approach involves redesigning your application to grab, say, 10 random sim_names, mark them as being taken by your process, exit the transaction. Then process them and finally release them in other transaction(s).

    This approach of mark and release not being part of the transaction containing the action — this is especially useful when the action takes a long time.

    Grabbing 10 is just as fast as grabbing 1; hence that gives you speed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search