Mysql – When do I need transactions /locks

innodblockingMySQLtransaction

I know that transactions are meant to group several operations as one.
But if for example in the same table one transaction does
DELETE FROM table where state = 'done'
and another does either
SELECT FROM table where state='new_row'
OR INSERT INTO TABLE VALUES (123, 'new_row')
do I need any transactions or locks for the DELETE vs INSERT/SELECT? It seems not but I was thinking I may be misunderstanding something

Note:
My question is not about transactions and their benefits.My question is if 2 statements from different processes on the same table SELECT/DELETE need for any reason to be synchronized

Best Answer

In general when running queries (SQL Statements) against a database you want all of the statements to successfully completed and if they do not revert data to what is was before you ran the sql statement. The overall concept you are looking for is ACID

In order to provide this functionality, Databases generally support the concept of a transaction.

When you start a SQL Statement with a Transaction you are telling the database that all statements within the transaction must complete successfully and if they do not the database will rollback(revert) your changes putting the database back to the way it was before you made a change.

Take the following two statements: 

Insert INTO UserName VALUES (123,'Test User')
Insert INTO UserCommission VALUES (123,0.5)

If you run the above statements without a transaction and the first statement failed then the second statement will still run thus inserting a row into the UserCommission table without an associated row in the UserName table.

If you then run the following

BEGIN TRANSACTION
Insert INTO UserName VALUES (123,'Test User')
Insert INTO UserCommission VALUES (123,0.5)
IF NO Errors Commit ELSE Rollback

In this scenario if the first statement failed you can check for errors and then rollback the transaction (sql statements) leaving the database as though you had never run the statement so you no longer have a row in the UserCmomission table without a corresponding row in the UserName table.

In general you only need a transaction when you are actually changing the data such as Insert, Delete, Update etc but sometimes as part of procedures you may have select statement to get some data that maybe use in Insert, Delete, Update etc statements.

Locks on the other had deal with how the database actually internally marks that the data is in use so that if other user are querying the same data the database knows that the data is in use.

Depending on how your table is constructed (keys, indexes etc), the size of the table etc and the type of action you are performing ( Select, Insert, Update, Delete) the database will perform many different types of locks such as page, table, row locks etc

Of course all of these can impact the performance of your queries and/or database.

Related Question