About database lock mechanism

1. How the database is locked, a simple sql statement
SELECT username,passwd FROM g_test LIMIT 1 FOR UPDATE
Add FOR UPDATE after a normal query is to add FOR UPDATE to the database Is it locked?
2. Adding FOR UPDATE locks write. Does this statement take effect on DELETE?
3. Is MYISAM locking a full table lock?
4.InnoDB is row lock?

New question

5. If the database opens a transaction and adds a lock, but for some reason the subsequent operations are not completed completely and there is no commit or rollback, is there a timeout mechanism for the transaction? Does the lock have a timeout mechanism? Automatically destroyed upon expiration

Reply content:

1. How the database is locked, a simple sql statement
SELECT username,passwd FROM g_test LIMIT 1 FOR UPDATE
Add FOR UPDATE after a normal query is to add FOR UPDATE to the database Is it locked?
2. Adding FOR UPDATE locks write. Does this statement take effect on DELETE?
3. Is MYISAM locking a full table lock?
4.InnoDB is row lock?

New question

5. If the database opens a transaction and adds a lock, but for some reason the subsequent operations are not completed completely and there is no commit or rollback, is there a timeout mechanism for the transaction? Does the lock have a timeout mechanism? Automatically destroyed upon expiration

What other respondents said is not comprehensive enough, let me sort it out for you.
First of all, I think that before talking about various database mechanisms (especially databases with plug-in storage engines like MySQL), you must declare the storage engine you use, otherwise the question is meaningless. Here I assume that your Questions 1 and 2 are based on the innoDB storage engine, and my theory is also based on the innoDB engine.
Clear the concept, there are two types of locks:
Read lock-> Shared lock (S)
Write lock-> Exclusive lock (X)
Writing operations are exclusive locks. The read operation is an exclusive lock.
Theoretically speaking, as long as the operated object (such as the row with id =1) has an exclusive lock, then except for this transaction that can operate this row, other transactions have no right to perform any operations on this row. Operation, whether it is a read operation or a write operation

By the way, select .. for update does not belong to the SQL specification, so its use should actually be avoided.
select ..for update performs an exclusive lock operation, but this statement is only meaningful in a transaction, because when used in non-transaction situations, the locking and unlocking operations are completed at once, without any practical meaning. .
Another very important point is that innoDB does not directly use row-level locks in transaction operations with isolation levels of REPEATABLE READ and READ COMMITTED, but uses the optimistic mechanism MVCC, that is, most read operations do not require additional locks. Locks, even write locks, only lock necessary rows "Understanding MVCC"

Back to your question:
1. Is "SELECT username,passwd FROM g_test LIMIT 1 FOR UPDATE" locked?

Yes, a row-level exclusive lock is added.

2. "Is the write lock for update valid for the delete statement?"

Plus for update is a write lock (i.e. exclusive lock), which is of course valid for delete (delete cannot operate), but it is still the above point of view. In the transaction operations of REPEATABLE READ and READ COMMITTED, use MVCC is installed, so you may be surprised to find that even after using the for update exclusive lock statement, other transactions can still perform read operations.

3. "Is MyISAM locking a full table lock?"

Yes, the MyISAM storage engine only has table-level locking (table-level locking)

4. "InnoDB is a row-level lock?"

Not entirely correct, innoDB has row-level locks and table-level locks, but it defaults to row-level locks.

5. "Do transactions and locks have a timeout mechanism?"

The transaction itself does not have a timeout mechanism, but if the transaction times out due to table locking, a rollback operation will be performed. The default lock timeout of innodb is 50 seconds, which is achieved by setting the variable innodb_lock_wait_timeout.

MYISAM locking is for the entire table; InnoDB locking is a row lock, for one row of records
SELECT username, passwd FROM g_test LIMIT 1 FOR UPDATE is to prevent multiple processes from executing SQL queries at the same time, plus FOR If you UPDATE, it will be locked, so that another process will queue up and wait until the previous process is completed; that is, the most important point of concurrency
is that the lock must be placed in the transaction, that is, sql execution Start the transaction first.

InnoDB uses a two-phase locking protocol. Locking can be performed at any time during transaction execution. Locks are only released when commit or rollback is executed, and all locks are released at the same time. InnoDB will automatically lock when needed based on the isolation level. This is an implicit lock;
InnoDB also supports explicit locking through specific statements:
select ... lock in share mode (shared lock)
select ... for update (exclusive lock)
In addition, MySQL also supports lock tables and unlock tables statements at the server layer, which has nothing to do with the storage engine.