Are locks implicitly applied when we query over mysql through sql? What type of locks are applied when i do a read from db, write from db? How does this locks function in databases that are used for real time applications?
Q: Are locks implicitly applied when we query over mysql through sql?
A: Yes.
Q: What type of locks are applied when i do a read from db, write from db?
A: That depends on the storage engine (MyISAM, InnoDB, et al.)
MyISAM takes "table level" locks. It uses "share" locks for read, and "exclusive" lock for writes.
InnoDB uses both "table level" and "row level" locks, and is influenced by the session isolation level (REPEATABLE-READ, READ-COMMITTED, et al.)
A complete discussion of MySQL locking is much to deep for an answer on StackOverflow.
Q: How does this locks function in databases that are used for real time applications?
A: The lock mechanisms function the same, the storage engine doesn't really differentiate between applications that are "real time" or otherwise. There is no specific "real time" setting for InnoDB locking.
Our application requirements will influence our database design and implementation decisions.