I have a general question on how to avoid deadlocks when reading from table which is being updated by another transactions.

In short, I have a loader software installed on the server, it runs multiple time a day as scheduled. It will download new data files from FTP and load them into the database.

In the meanwhile, I also have the need to read from those tables whenever a user calls. I have seen some times, it throws out errors like this, if the table I was trying to read from, happen to be locked as the loader is writing to the same table.

Transaction (Process ID xx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction'

I was wondering if there is a optimal way to let the user still be able to read even if it is being locked. Some suggests set the isolation level to read uncommited (dirty read), but my concern is dirty read is very incosistant and it might return duplicates.

Ideally, I want to read committed data only but still avoid deadlocks. Any better idea on this?

CodePudding user response：

Based on what you have told us, the only way is to use dirty read or schedule the updates when you don't need users to access the system. (eg at night).

However, there may be changes you can make to your process that would solve your problem. Some examples:

Use smaller batch sizes

If you currently to a load and write 1000 records to the table, all other processes have to wait for that to finish. If you change to 100 records, that might be fast enough to allow the other processes to do there read between the 100 record updates.

Validate code is only locking when it needs to do so

Often code is written sloppy. You should do a code review and make sure the update process is only locking tables when it needs to. Often a connection for lookup tables is left open while other code runs for example. Other cases where the DB is locked when it does not need to be can occur. A good code review should allow your update code to be more frendly.

Avoid complicated SQL during updates

Sometimes the SQL itself can be rewritten in a way that reduces the locking time. For example imagine an update that requires a number of lookup tables. You could put that all in the update statement with JOINs or you can do it first (in a way that does not lock) and then not have any JOINs in your update. The 2nd way might not be as fast overall, but will have smaller lock periods.

CodePudding user response：

Ideally, I want to read committed data only but still avoid deadlocks. Any better idea on this?

Turn on the READ COMMITTED SNAPSHOT database option or read with the SNAPSHOT isolation level. Then your readers won't be blocked by your writers and can still only read committed data.