Database interview question 2

This is the 10th day of my participation in the Gwen Challenge.More article challenges

MySQL exclusive lock & shared lock

Mysql lock mechanism includes table level lock and row level lock. Shared and exclusive locks are row-level locks.

Shared lock: read lock, S lock for short. A shared lock means that multiple transactions can share the same lock for the same data. All transactions can access the data, but they can only read the data and cannot modify it.
Exclusive lock: Write lock, X lock for short. An exclusive lock means that it cannot coexist with other locks. For example, if a transaction acquires an exclusive lock for a data row, other transactions cannot acquire other locks for the row, including shared locks and exclusive locks. However, a transaction that acquires an exclusive lock can read and modify data on its own.

A shared lock is a simple way to understand that multiple transactions can only read data and cannot modify data. A shared lock is a simple way to understand an exclusive lock. I made the mistake of thinking that once a row is locked, other transactions cannot read or modify the row.

An exclusive lock means that after a transaction has an exclusive lock on a row of data, other transactions cannot have another lock on it. Mysql InnoDB engine default to update data,delete data,insert data automatically associated with the exclusive lock type, the default select statement does not add any lock type. For update statement, add shared lock, select… Lock in share mode statement. SQL > select * from ‘for UPDATE’ and ‘lock in share mode’; SQL > select * from ‘for update’; from… Query data because normal queries do not have any locking mechanism.

MySQL transaction isolation level

1. Read uncommitted

Dirty reads are allowed, that is, data that may be read from other sessions that have not committed transaction changes (generally not used by databases)

2. Read COMMITTED

Only committed data can be read. Most databases, such as Oracle, default to this level (no repeat reads)

Data is read without locking, but data is written, modified, and deleted without locking

Repeatable read (Repeatable read)

Repeatable. Queries within the same transaction are consistent at the start of the transaction, InnoDB default level. In the SQL standard, this isolation level eliminates non-repeatable reads, but phantom reads still exist

4. Serializable

Fully serialized reads require table-level shared locks for each read and block each other. Solve the phantom read

5. The difference between unrepeatable reading and phantom reading

In repeatable reads, the SQL locks the data after the first read so that no other transaction can modify the data and then repeatable reads can be implemented

However, this method does not lock the insert data, so if transaction A has previously read the data, or modified the whole data, transaction B can still insert the data commit, then transaction A will find that there is no reason for the extra data, this is A phantom read, cannot be avoided by row locking

Serializable isolation level is required, read with read lock, write with write lock, read lock and write lock mutually exclusive, this can effectively avoid magic read, unrepeatable read, dirty read and other problems, but will greatly reduce the concurrency of the database.

Problems with Redis cache

1. Cache penetration

define

A large number of requested keys do not exist in the cache, resulting in the request directly to the database, not through the cache layer (for example: a hacker deliberately created a key does not exist in our cache to initiate a large number of requests, resulting in a large number of requests to the database)

The solution

Cache invalid key

If neither cache nor database can find a key, write it to Redis and set the expiration time.

However, it cannot fundamentally solve the problem. If hackers maliciously attack and build different request keys each time, a large number of invalid keys will be cached in Redis
Bloom filter

However, the Bloom filter can be misjudged. The bottom line: When a Bloom filter says an element is present, there’s a small chance it’s wrong. The Bloom filter says that an element is not there, so that element must not be there.
What happens when an element is added to a Bloom filter:
1. The element value is computed using the hash function in the Bloom filter to obtain the hash value (there are several hash functions that yield several hash values).
2. Based on the resulting hash value, the value of the corresponding subscript in the bit array is set to 1.
What do we do when we need to determine whether an element exists in a Bloom filter?
1. Perform the same hash on the given element again;
2. If the value is 1, then the value is in the Bloom filter. If there is a value that is not 1, then the element is not in the Bloom filter.

Cache avalanche