MySQL Notes

MySQL Keywords and Queries

Update Records by joins

UPDATE screening, babies SET screening.baby_id = babies.id WHERE screening.baby_username = babies.baby_username

MySQL Table Locking

A lock is a mechanism associated with a table used to restrict the unauthorized access of the data in a table. MySQL allows a client session to acquire a table lock explicitly to cooperate with other sessions to access the table's data. MySQL also allows table locking to prevent it from unauthorized modification into the same table during a specific period.

A session in MySQL can acquire or release locks on the table only for itself. Therefore, one session cannot acquire or release table locks for other sessions. It is to note that we must have a TABLE LOCK and SELECT privileges for table locking.

Table Locking in MySQL is mainly used to solve concurrency problems. It will be used while running a transaction, i.e., first read a value from a table (database) and then write it into the table (database).

Mysql provides two types of locks onto the table, which are:

READ LOCK: This lock allows a user to only read the data from a table.

WRITE LOCK: This lock allows a user to do both reading and writing into a table.

It is to note that the default storage engine used in MySQL is InnoDB. The InnoDB storage engine does not require table locking manually because MySQL automatically uses row-level locking for InnoDB tables. Therefore, we can do multiple transactions on the same table simultaneously to read and write operations without making each other wait. All other storage engines use table locking in MySQL.