3

I'm new in web programming with SQL database. So please forgive my ignorance.

I'm using some modern framework to make a REST server with a SQL database. I'm using transaction only when I think I need it. But during the development I find out that parallel operation land me to dead lock and inconsistent data so I started to add more transactions in the SQL queries.

Going on with the dead lock paranoia I'm approaching the idea that each REST request should have been coupled with an SQL transaction.

This way is more safer but the database is locked until the request is responded and.. I'm looking for more scalable solution. Any hints on what should be the right way to handle this problem a part of using the paradigma transaction-per-request?

  • Is this an actual performance issue or more of a curiosity? I actually create a transaction per request and haven't had any issue so far. It's not uncommon to need all or nothing requests. You can look into transaction isolation levels for other options. – Sirisian Feb 26 '16 at 22:23
  • It is more a curiosity because my system is not online yet. Can you provide dome more information about transaction isolation levels please? – nkint Feb 27 '16 at 2:28
2

The point of a database transaction is so you can be sure that several facts within the database are true simultaneously, despite there being other users writing to the same database concurrently.

Take the cannonical example of transferring money between bank accounts. The system must ensure that the source account exists, has sufficient funds, the destination account exists, and that both debit and credit happen or neither happens. It must guarantee this while other transactions happen, perhaps even between these two accounts. The system ensures this by taking locks on the tables concerned.

What locks are taken, and how much of other peoples' work you see, is controlled by the transaction isolation level. There are a couple of recognised isolation levels and each DBMS has implementation-specific variations. Locks should not be feared. They exist to protect data and ensure consistency in the event of a rollback or system crash.

The flipside of locks is concurrency. This is how many different tasks can happen against a single database simultaneously. Obviously the more locks one user has taken, the more another user is likely to be blocked. Ultimately deadlocks may occur, as you've seen.

To avoid deadlocks, keep transactions short (but no shorter than necessary to ensure consistency) by performing the SQL statements as close together as possible. Commit the transactions as soon as the work is done. Read the tables in a specific sequence when possible. This promotes queuing behaviour and reduces deadlocks. Ensure you have well-tuned queries and good indexes. Increase server RAM so the working set is in memory and queries complete faster. Process small amounts of data in a DB transaction; ideally one business transaction per DB transaction. Code the application to catch deadlock error codes and re-submit the work.

In any reasonably complex application deadlocks are inevitable, eventually, but they need not be catastrophic.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.