mysql transaction vs lock I have a question regarding MySQL locking and transaction management.
 My Question is ..does mysql takes lock on the the tuple/table on which I am doing select/update in a transaction ?
With MyISAM, you can use the concurrent_insert=2 optimization, where an INSERT will not block existing or new SELECT statements (but will block anything else).

With InnoDB, the rules are:


SELECTs never block
An UPDATE or DELETE on a row will place
a lock on said row. But this can work even while the same row is
being SELECTed.  
A second update (on concurrent transaction) on
same row will block, until the first is committed or rolled back.
INSERTs are more complicated: if the table has an AUTO_INCREMENT, then that is locked (though other concurrent operations are allowed if not competing for AUTO_INCREMENT)
Some concurrent INSERTs may block one another because locking is done over the clustered index.
DELETEs, UPDATEs on ranges are also more complex and may involve firther blocks due to locks on clustered index.


This is probably not a thorough list, but should get you the general picture.