We are using MYSQL database for an Analytics app. I have two table
token-big (for overview of actual data) both have
id(int) unix_starttime(int) unix_endtime(int) details(varchar 256)
A row is added to table
token every second using python MySQLdb. I have an after insert trigger on
token table which does the following:
- Get the latest entry (last) from
unix_starttimeof newly inserted row (in
token) is no more than 5 seconds apart from
unix_endtimeof last row in
unix_endtimeof newly added row in
- else insert new row in
token-bigsame as the newly added row in
This works fine for a few
token-big table pairs but I start to get deadlocks if these pairs (tables) are in number of hundreds.
How can I scale this method? Is there any better alternative?
I don't wish to use
events as it may delay the data shown to user. Same goes for background jobs which run every few minutes or so.