0

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

1

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.

| improve this answer | |
  • My question was not about transactions and their benefits.The question was if 2 statements for different processes on the same table SELECT/DELETE need for any reason to be synchronized – Jim Apr 8 '14 at 17:55
0

I'm not 100% sure I understand your question but anyway. Generally there's no need to wrap SELECT statements in transactions because they are read only, although you can specify the isolation level you want the SELECT to use (READ UNCOMITTED for instance). See here for a question about locks caused by SELECT statements in MySQL and how to change the type of read.

DELETE and INSERT will automatically lock records as they have to to work, but you might want to transactionalise them to allow you to roll back in the case of an unexpected error or if you have a sequence of events where later statements depend on the success of earlier ones and you don't want to leave data in an uncertain state in case of failure.

| improve this answer | |
  • DELETE and INSERT will automatically lock records as they have to to work by this you mean each row as they access it?Like which ever query does first? – Jim Apr 8 '14 at 17:54
  • I believe that to be the case, however I know that locking in MySQL can be quite complex (as opposed to SQL Server which defaults to exclusive table locks for DELETE statements). I suspect you would be best served by having a read of the documentation on locks in MySQL and this explanation for InnoDB of locks set by various statements. There are differences depending on whether you are using MyISAM or InnoDB. – Steve Pettifer Apr 9 '14 at 7:21

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.