MySQL

Question 1

What 3 levels of MYSQL database consist from?

Answer 1

1. Connection and security ( Check if you can get access to data)
2. Optimization and performing ( overbuilding request, caching, select indexes etc..)
3. Respond to requests

Question 2

What kind of locking do you know?

Answer 2

1. Row-Level Locking ( blocks only specific row)

2. Table Locking ( blocks all table )

Question 3

What is ACID?

Answer 3

ACID is an acronym that describes four properties of a robust database system: atomicity, consistency, isolation, and durability.

• Atomicity
• Consistency
• Isolation
• Durability

Question 4

What is transaction isolation level? What transaction isolation levels do you know?

Answer 4

Isolation levels define the degree to which a transaction must be isolated from the data modifications made by any other transaction in the database system

• Read uncommitted (Dirty read )
• Read committed (Non Repeatable read)
• Repeatable read (Phantom Read )
• Serilializable

Question 5

What is database transaction deadlock?

Answer 5

In a database, a deadlock is a situation in which two or more transactions are waiting for one another to give up locks.

For example, Transaction A might hold a lock on some rows in the Accounts table and needs to update some rows in the Orders table to finish. Transaction B holds locks on those very rows in the Orders table but needs to update the rows in the Accounts table held by Transaction A. Transaction A cannot complete its transaction because of the lock on Orders. Transaction B cannot complete its transaction because of the lock on Accounts

Question 6

Where do we store MySQL tables?

Answer 6

In file system. MySQL creates file with .frm extension for each table and store data there ( for example animal.frm)

Question 7

What is MVCC?

Answer 7

Multi versioning concurrency control (MVCC) is a database design theory that enables relational databases to support concurrency, or more simply multiple user access to common data in your database.

Question 8

What kind of measure for testing db performance do you know?

Answer 8

• Throughput ( How many transaction per second available?)
• Response time ( How long time response can tike? )
• Paralelizm ( How many thread can work in the same time?)
• Scalability ( How will your system behave if you double number of users? Linear dependency or other? )

Question 9

What steps you can do to test your database?

Answer 9

• Create a snapshot of your db (anon db)

- Save all request in the most loaded time ( from logs)

Question 10

What is Profiling?

Answer 10

MySQL query profiling is a useful technique when trying to analyze the overall DB performance ( Gives you information about db performance)

Question 11

Why do you need trigger?

Answer 11

You can use a trigger to backfill table column with duplication values to optimize select request from the table ( denormalization)

Question 12

What is a shadow copy?

Answer 12

It allows you to create a new table, do ALTER operation there to not block the main table, then replace them.

Question 13

What commands can block the whole table?

Answer 13

• ALTER TABLE

Question 14

What can slow down your DB requests?

Answer 14

• When you request more data than you need and then filter it on the program level ( Request unnecessary rows)
• When you request the same data on the same page many times instead of caching
• When we use a full scan table instead of indexes
• When you send many small requests and combine them instead of creating one big ( For example you request to select each column name 10 times one by one instead of 1 request to get all together)
• Hardware problem. When you send too many concurrency requests at the same time

Question 15

How caching understand that existing data haven’t changed (not outdated )?

Answer 15

• Looks like insert/update/delete operations can affect cache and invalidate it
• The second option is the expiration time for cache

Question 16

What is a database table partitioning ( секционирование )?

Answer 16

Partitioning is the database process where very large tables are divided into multiple smaller parts. By splitting a large table into smaller, individual tables, queries that access only a fraction of the data can run faster because there is less data to scan. The main of goal of partitioning is to aid in maintenance of large tables and to reduce the overall response time to read and load data for particular SQL operations.

Question 17

What is a trigger?

Answer 17

Trigger allows to run code after one of the following operations CREATE, UPDATAT OR DELETE

Question 18

What is database event?

Answer 18

This is something similar to cron job. It allows you to run code at a specific time.

Question 19

What is distributed transaction?

Answer 19

A distributed transaction is a set of operations on data that is performed across two or more databases. ( transaction between several databases)

Question 20

What is replication? Why do we need it?

Answer 20

Replication is a copy of DB on another server.

We need it to:

• distribute load between several servers
• backup data
• switch on to replica when emergency
• testing new features

Question 21

What relations between replications servers you know?

Answer 21

• One main server and many child servers
• Main to main ( bidirectional )
• Main to main ( bidirectional but the second one is passive can only read data)

Question 22

What is scalability?

Answer 22

It’s the ability to scale the system when it’s necessary and has a good proportion between resources and workload.

Question 23

What kind of scalability do you know?

Answer 23

• Vertical scaling ( Improve current hardware )

- Horizontal scaling ( Adding more hardware to a system)

Question 24

How to implement horizontal scaling?

Answer 24

• Create replications (read only)

- Separate debates to independent parts ( as contexts )

Question 25

What is Database sharding?

Answer 25

Database sharding is the process of splitting up a database across multiple machines to improve the scalability of an application. ( For example move all user’s info to the separated shard)

Question 26

What is Atomicity ( Transaction)?

Answer 26

Is an all-or-none proposition. Transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. There must be no state in a database where a transaction is left partially completed.

Question 27

What is Consistency ( Transaction )?

Answer 27

It’s application responsibility, not DB. The database must remain in a consistent state after any transaction. ( That’s mean we should write data that we don’t expect to be added during transaction, no side effect something like clear function)

Question 28

What is Isolation ( Transaction )?

Answer 28

Isolation - keeps transactions separated from each other until they’re finished. ( Don’t allow to update any data inside transaction until it finished)

Question 29

What is Durability (Transaction) ?

Answer 29

Durability (Сохраняемоть). If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data. If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action. ( If transaction was successful that mean that data should be saved in any case)

Question 30

What is Read uncommitted (Dirty read) isolation level?

Answer 30

Read uncommitted (Dirty read ) - Other transactions have access to data inside transactions.

Question 31

What is Read committed transaction isolation level?

Answer 31

Read committed (Non Repeatable read) - Other transactions can see only data that was successfully finished by other transactions ( The second transaction will be wait until the first finish then can overide this data)

Question 32

What is nonrepeatable read ( transaction isolation level)?

Answer 32

Non-repeatable reads are when your transaction reads committed UPDATES from another transaction. The same row now has different values than it did when your transaction began.

Question 33

What is phantom read ( transaction isolation level )?

Answer 33

Phantom reads are similar to nonrepetable read but when reading from committed INSERTS and/or DELETES from another transaction. There are new rows or rows that have disappeared since you began the transaction.

Question 34

What is SNAPSHOT isolation?

Answer 34

SNAPSHOT isolation specifies that data read within a transaction will never reflect changes made by other simultaneous transactions. The transaction uses the data row versions that exist when the transaction begins.

Question 35

What is lost update problem in transactio?

Answer 35

In the lost update problem, update done to a data item by a transaction is lost as it is overwritten by the update done by another transaction. Example: transaction 1 changes the value of X but it gets overwritten by the update done by transaction 2 on X

Question 36

What is write skew in Transaction?

Answer 36

When 2 concurrent transactions modifies 2 different objects and making race conditions. For example if 2 persons want to buy the last ticket and they do it at the same time and system sold the last ticket twice.

Question 37

What is Two Phase Locking in Transaction?

Answer 37

Two Phase Locking Protocol also known as 2PL protocol is a method of concurrency control in DBMS that ensures serializability by applying a lock to the transaction data which blocks other transactions to access the same data simultaneously. ( Block data untill transaction is finished)