I have a system writing batches of queries as transactions (to maintain coherency), these can be sets of 30-100 queries per transaction. These are done on multiple tables
At the same time there are read queries on the same tables using inner selectes - SELECT FROM table1... IN (SELECT from table 2...)
To clarity - there is one writer thread writing the transactions (each transaction writing to all tables) and many reader threads.
I am getting deadlock errors. 1. could the deadlock be related to the IN selection? 2. how can I make the read queries provided only commited data, but not necessarily wait for current transactions to commit (meaning it can return data previously committed) 3. could the deadlock be a result of a transaction that is simply taking too long to complete?