Database transaction and locking

Question

I am trying to learn using databases correctly by myself and now I realised, that although I have used these concepts before, I don't really know what's the difference between them:

What is a transaction? What is a lock?

As far as I know, when using a transaction, all instructions are executed as one, so another user can not interfere. So why would I use a lock in this case? When should I use locking and when transactions?

I am using MySQL, if it matters...

Answer 1

Transactions are used when you are executing queries and wish to have a mechanism to reverse the resulting modifications if there is a possibility of error during their execution.

Locking in mysql is used to gain exclusive mode to certain table and perform much faster insert/delete queries, most often for bulk data upload into a heavy usage database.

Answer 2

The transaction is a unit, it is necessary for guaranteed atomicity, or all of these steps are done or not done any. It is necessary to do so because normally the operations on the database require you to perform various operations of reading, writing, and others, and only successful execution of all ensures compliance to the target.

To better understand read about ACID (atomicity, consistency, isolation, durability), these are the characteristics that guarantee that a transaction is processed reliably.