DB
-
[DB] DB Lock & 2PL ProtocolDB/DB Basic 2024. 3. 31. 21:37
DB Lock : Machanisms used in database management systems to control concurrent access to shared resources such as database tables, rows, or even specific data items. - Ensure data consistency and integrity by preventing conflicts between concurrent transactions Types 1. Shared Lock (S-Lock) - Allows multiple transactions to read a resource simultaneously - Prevents write operations by other tran..
-
[DB] Concurrency Control (2) Concurrency Problem and Isolation LevelDB/DB Basic 2024. 2. 27. 00:11
Concurrency Problem and Isolation Level Concurrency Problem Concurrency problems occur when multiple transactions are being executed on the same database in unrestricted problems. Concurrency Problem Description Lost Update Occurs when two transactions try to update the same data concurrently, pontentially resulting in one transaction's changes being overwritten by the other Dirty Read Happens w..
-
[DB] Concurrency Control (1) Schedule, Serializability, RecoverabilityDB/DB Basic 2024. 2. 25. 22:56
Concurrency Control When multiple transactions are processed simultaneously in a single DBMS, the utilization of processors and disks increases, leading to an increase in throughput (the number of transactions processed per unit time), and there is a benefit of reduced response time as shorter transactions do not need to wait for other transactions. However, because multiple transactions are exe..
-
[DB] TransactionDB/DB Basic 2024. 2. 24. 16:53
Transaction : A logical unit of work in a database that consists of one or more operations performed on the database ACID 1. Atomicity - Transactions are all-or-nothing - Ensures that all operations within a transaction are completed successfully, or none of them are - If any part fails, the entire transaction is rolled back 2. Consistency - Maintains the database in a valid state before and aft..
-
[DB] Stored Procedure in 3-tier architecture : pros and consDB/DB Basic 2024. 1. 22. 23:33
3-Tier Architecture 1. Presentation Layer (User Interface) - Topmost layer that is responsible for interacting with end-users (e.g. web pages, mobile apps, etc.) - To present information to users and gather user input(forwards user requests to the next layer) 2. Business Logic Layer (Application Layer) - Processes the user input received from the presentation layer and coordinates the applicatio..
-
[MySQL] Stored ProcedureDB/MySQL 2024. 1. 22. 12:51
Stored Procedure - A set of SQL statements that are stored on the server and can be executed as a single unit - Precompiled and stored in the database, allowing for improved performance and code reusability Procedure Declaration - A stored procedure is defined using the 'CREATE PROCEURE' statement - Includes the procedure name, parameters(if any), and the body of the procedure /* Basic sysntax *..
-
[MySQL] Stored FunctionDB/MySQL 2024. 1. 21. 22:20
Stored Function - A set of SQL statements that perform a specific task and return a single value - Allow to encapsulate a series of SQL statements into a reusable and modular unit DELIMITER // -- change DELIMITER ";" to "//" temporarily CREATE FUNCTION calculate_area(length DOUBLE, width DOUBLE) RETURNS DOUBLE BEGIN DECLARE area DOUBLE; SET area = length * width; RETURN area; END // DELIMITER ; ..
-
[MySQL/Java] DB 쿼리 성능개선 (2) 랭킹 조회 속도 개선하기 (5s ➡️ 1s대로 줄이기!)DB/MySQL 2023. 6. 29. 21:59
기능 배경 - 개선 대상 : 랭킹 조회 기능 - 랭킹 조회 api 요청시 해당 게임의 Top3에 대한 정보를 DB에서 조회 ➡️ ① Top3 정보 중 하나가 요청자와 일치할 경우 그대로 정보를 담아 return ➡️ ② Top3 정보에 요청자가 없을 경우, 해당 사용자의 등수와 정보를 DB에서 재조회하여 담은 후 return 기존에 해당 기능을 수행하기 위해 작성한 QueryRepository의 코드는 아래와 같다. QueryDSL을 사용해서 작성했는데.. 아무래도 QueryDSL을 잘 알지 못한 상태에서 코드부터 적어서 부족한 부분이 많아 보인다..! 또, 프로젝트였기 때문에 데이터가 많을 경우를 고려하지 않고 아주 미니미한 목데이터 안에서만 테스트를 했기 때문에, 일단 쿼리로 데이터가 "날라만 오..