[RESOLVED] Transactions or Lock tables or both??

[stewartship]

Hi all,

Very general question about using transactions and lock tables. Do you use both in conjunction with eachother or one rather than another in certain scenarios etc.?

Does transactions BEGIN, COMMIT, ROLLBAck etc. stop others from updating the table at the same time or just ensures that that particular transaction occurs completely or not at all?

Using LOCK TABLES on InnoDB and using MySQL i read some material on the fact that both the InnoDB and MySQL get locks and that this can lead to possible deadlocks, does anyone know more about this?

SO in general just looking for some insight into Transactions and LOCKS and maybe some scripts to get a proper visual of how they work.

Many Thanks

[MarkR]

This depends entirely on what you're trying to achieve.

Normally with MyISAM, MySQL acquires table locks in such a way that two processes can't interfere with one anothers queries or see partially done updates (e.g. all UPDATE, INSERT statements appear atomic).

This doesn't allow you to roll back, but it does guard against multiple processes trying the same thing at once. Some types of queries do useful things atomically e.g. inserting with a uniquely indexed column fails if there is one already.

InnoDB does it completely differently. In InnoDB, two INSERTs in the same table (that has multiple indexes) can actually deadlock because of the internal locking mechanism for indexes. This is highly undesirable and annoying. Likewise, other queries (e.g. updates) may be able to.

If you're doing a lot of high performance data modification, I strongly recommend MyISAM if you can't handle these deadlock failures.

MyISAM gets a lot of flak for not having row-level locking. But table-level locking is actually quicker in many cases- you only lose performance if two threads would have been able to work concurrently (e.g. updating non-indexed columns in different rows).

Mark

[stewartship]

Hey Mark

Thanks for the info!

The system i am designing is a CRM, stats producer and ordering system for a logistics company. The particular area i am worried about is the actual ordering, as they will be multiple users but each order must have a unique identifier, this is produced as they initiate the order.

At present the table is InnoDB which is used to store the order details and its identifier. So of course my worry was that two request for new orders would arise, and the production of the unique identifier using auto_increment might cause problems unless the table being used was locked, until one transaction had been completed, and then the next request could be done. As you stated with InnoDB and two inserts could get a deadlock!

Should i change the table?

Thank you for your time.

[MarkR]

The trick would be, as soon as someone begins an order, insert a row. Then continue to modify that row as the order is modified (plus any rows in tables which have a foreign key such as order lines, payment info etc).

Then once the order is confirmed or "raised" etc, just do a status change.

An arbitrarily large number of incomplete orders could be in the system.

This would of course not require any explicit locking or transactions. Transactions cannot span over several pages anyway.

MySQL will never reuse an auto_increment column. It always assigns them uniquely. In the case where another uncommitted transaction was in progress, the thread would either get given a new number, or be blocked (I'm not sure which).

Mark

[stewartship]

Many thanks Mark that clarified it for me appreciate the help!!