Forem: dwivedialind

File locking vs Row Level Locking

dwivedialind — Mon, 17 Jun 2024 15:34:03 +0000

This is a submission for DEV Computer Science Challenge v24.06.12: One Byte Explainer.

Explainer

Have you ever wondered how can multiple users perform DML operations on same table at the same time? Because of row level locking in RDBMS. In RDBMS every row is a file instead of table as a file.
Rows inside a table is scattered (fragmented) all over the DB server HD, to speed up INSERT statement.

Additional Context

Consider a multi-user environment, if multiple users are inserting rows simultaneously into the same table, and if MySQL were to store the rows sequentially then it would be really slow.
That's why when you insert a row into the table, wherever it finds the free space it will store the row there.

Git

dwivedialind — Sun, 16 Jun 2024 11:25:33 +0000

Source Code Management(SCM)

Version Control System(VCS):

VCS/SCM/Revision Control System(RCS) are software tools for keeping track of changes to code over time

Developers can collaborate on different projects, separating their tasks through branches.
Look back to earlier versions for comparing and fixing the mistakes if needed.

Version

Copy of document taken at a specific time
records
- author
- time stamp
- changes

Benefits of Using VCS

Long-term change history

Changes made by developers over the years, can be seen in history.
Allows going back to previous version for analyzing bugs and fixing problems.

Branching and Merging

(Branching)No interference with each other's work.
(Merging)Brings the work together and seeing if there are conflicts between those works.

Traceability

Trace each change and connect to project management or bug-tracking software.
Commits on message helps describing purpose of the change.

Synchronization
- fetch up-to-date codes from repository
Backup and Restore
- Files are saved at any time
- Restored from last saved one
Undoing
- Can undo last known version and last one created long time ago.

Types of VCS

Local Version Control System
Centralized Version Control System
Distributed Version Control System

Local VCS

Source Safe is one of obsolete Source Control System

Prevent data loss from modifications to the actual file content.
Avoid confusing directories

Directory Proliferation: Without VCS, you might create multiple directories to save different versions of your project. This become difficult to manage and remember which version is which.
Misplacement: Misplace files in wrong directory or forget which directory contains latest version of your files.

Prevent Accidental Overwrites and Misfiled Copies

Overwriting Files: While manually copy files to create a new version, might overwrite existing files accidentally, losing important changes.
Wrong Copies: Accidentally copy files to the wrong location, causing confusion about which files are most recent or which ones are correct.

Limitations:
- Individual Use
- No Collaboration
- Single Point of Failure

Centralized VCS

All versions of project stored in a single central repository(server).

Central Repository
- All files and version at one central location
- Acts as the single source of truth for the project.
Client-Server Model
- Users get files from and send changes to central server.
How it Works:
- Checkout: User gets a copy of project from central repository.
- Edit: Users make changes to local copy.
- Commit: Changes sent back to central repository.
- Update: Users get latest changes from central repository.
Benefits
- Centralized Management
- Simplified Access Control
- Collaborative
Challenges
- Single Point of failure
- Scalability Issues: Performance can suffer with large teams or projects.
- Network Dependency: Requires network connection to interact with central repository.

SubVersion, PerForce, CVS(Concurrent Version System)

Distributed VCS

Local Repository:
- Every user has a complete copy of the entire repository, including all history.
- Users can commit changes to their own local repositories without needing network access.
Peer-to-Peer Model:

Users can share changes directly with each other or synchronize with a central server if needed.

No Single Point of Failure:
- Each user's local repository is a complete backup of the project.

Git, Mercurial, Bazaar or Darcs

Git

Designed and Developed by Linus Trovalds for Linux Kernel development
Free software distributed under the terms of the GNU General Public License version 2

Characteristics:

Strong support for non-linear development [Branches]
Distributed Development
Compatibility with existent systems and protocols
Efficient handling of large projects
cryptographic authentication of history
Toolkit-based design
Pluggable merge strategies

Advantages

Free and open source
Fast and Small
Implicit backup
Security
No need of powerful hardware
Easier Branching

Git Workflow

git init

Initialize a new empty Git repository or to reinitialize an existing one
Repository is a directory which contains the working directory along with metadata in .git

# Get the current status of repository(working directory+ staging area)
> git status
# Get the status with short style
> git status -s
?? file.js
# status will be represented in two characters
# 1. status of file against the staging area
# 2. status of file against the working directory

?? -> The file is untracked (the file is not known to the repository or the repository has not yet created any version of this file)

Staging Area A temporary collection before you add your file to the repository.

# To add file from working directory to staging area
> git add <file_name>

> git status -s
A  file.js
# the changes to file are added to the staging area

git commit

Committed snapshots are considered safe versions of a project because Git asks before changing them.

# commit the changes to the repository
# - any version in repository will contain
#       - author(user who has committed the changes)
#       - date and time of commit
#       - message used while committing the changes
#       - actual changes (added and deleted lines/files)
> git commit -m <commit_message>

git log

shows committed snapshots
only works on the committed history

> git log --oneline --color --graph

# --oneline: show only one line description
# --color: show colors while showing the logs
# --graph: show the branch graph

git status

# Modify the file.js
> git status -s
# _M: the file is modified and present in working directory

# add all the changes in the current directory to staging area
> git add .

> git status -s
# M_: the file is modified and present in staging area

git diff

gets the difference of current version of file with last recorded version

*git checkout

if the last recorded version in staging area, then git checkout will restore to that version.
else to last committed snapshot

#remove all the changes by replacing last recorded version with current version. You cannot retrieve the changes
> git checkout file.js

git reset

if changes are staged git checkout won't work
Unstage the changes

# remove the changes from staging area and move them back to working directory
> git reset

# unstage the changes and remove them immediately
# (along with all the changes in the working directory)
# Note: please execute this command on your own risk

> git reset --hard

Git Internals

- git repository (.git directory)
    - HEAD
    - config
    - description
    - hooks
    - info
    - objects
        - on commit, git creates the objects(files) in this directory
        - there are 4 types of objects
            - commit object
                - created per commit
                - it contains the information
                    - tree object id
                    - author
                    - committer
                    - commit message
            - tree object
                - created per commit per directory
                - it contains the information
                    - 100644: the entry is pointing to a file
                    - blob: type of object
                    - blob object id: the id of blob object
                    - file name: the name of the file which is having the contents stored in the blob file
            - blob object
                - created per file
                - it contains the contents of a file in encrypted format ( use git cat-file command to read the contents)
    - refs
        - heads
            - points to latest commit id
        - tags

id's here are in the form of hash value

# get the contents of internal object
> git cat-file -p <object-id>
# object-id: <dir_name>+hash
# git cat-file -t 6b3a

if two files have same content then blob object will be same for them
After making changes to file and commit, the commit object contains parentId which is the previous version of objectId

Git Branching

A way to write code without affecting the rest of your team.
Generally used for feature development.
Once confirmed, can merge the branch in the master branch and release the build to customers.

Branch is a file under .git/refs/heads with branch name, which stores latest commit id.
Default branch name is either main or master
git branch

Get list of branches

# create a branch
> git branch <branch_name>
# main and another created branch would point to same commit id

# to switch to branch
# git checkout <branchname>
> git branch b1

# create a new branch and checkout immediately
> git checkout -b b2

Merging

Merge new_branch in main

# merge b1 in main
> git checkout main
# show branch name
> git status
> git merge b1

Delete a branch

# delete a branch
> git branch -d <branch_name>

> git push origin --delete <branch_name>
#deletes a branch from a remote repository after pushing any unpushed commits.

> git log --graph --oneline --all --decorate

Checkout to commit id

> git checkout <commit_id>

Database1

dwivedialind — Sun, 16 Jun 2024 06:03:11 +0000

Database Concepts

MySQL v5.7 (RDBMS)
Oracle 11g(ORDBMS)
MongoDB (NoSQL DBMS)

DBMS(Database Management System): Software that helps to manage data

Various DBMS available
MSExcel, FoxBASE, FoxPro, DataEase, DataFlex, Clipper, DB Vista etc

Some Important Definitions

RDBMS: Relational Database Management System
ORDBMS: Object Relational DBMS (RDBMS+OODBMS)
OODBMS: Object Oriented DBMS
Database: Collection of Large amounts of data.
ANSI definition of DBMS : collection of programs that allows you to perform CRUD operations of data.

Computer: Processing(work done by computer) raw data to meaningful data/ processed data/ information.

DBMS vs RDBMS

Difference in Nomenclature

Field - attribute/column
Record - tuple/row/entity
File - table/relation

Relationship between two file is maintained programmatically.
vs
Relationship between 2 tables can be specified at the time of table creation using constraint.
More Programming
vs
Less Programming
More time required for s/w development
vs
Less time require for s/w development

Let's take an example of Server with Employee data is located in Delhi and Client in Pune,
Now In DBMS to access Employees with Salary > $1000, we have to copy files to Pune then after processing, send the data back to Delhi (Server location)
vs
In RDBMS we can process data on server only.

High Network Traffic
vs
Low Network Traffic
Slow & expensive
vs
Faster (in terms of network speed) and cheaper (in terms of hardware cost, network cost, infrastructure cost)
Processing on Client Machine
vs
Processing on Server Machine(Client-Server Architecture)

Most of the RDBMS Support Client-Server Architecture. (exception is MS Access->local Database on same machine)

File level Locking(not suitable for multi-user) vs Row level Locking(internally table is not a file, internally every row is a file)

Suppose there is a Server located in Delhi, with Pune and Hyderabad as client, now in DBMS is Pune tries to update any table in Server then that table is locked for Hyderabad
vs
In RDBMS only row is locked .

Distributed databases not supported
vs
Most of the RDBMS support Distributed Databases (Banking system is an example of Distributed Databases)
No security (of data)
- DBMS is dependent of OS for security
- DBMS allows access to the data through the OS
- Security is not a built-in feature of DBMS vs Multiple Levels of Security
- Logging in Security (MySQL database username and password)
- Command level Security (permission to issue MySQL commands)
  - create table, create function, create user, etc
- Object level Security (access to table and objects of other users)

Various RDMBS available:
- Informix (fastest in terms of processing speed)
- Oracle (most popular RDBMS)
- works on 113 OS
- 63% of world commercial DB market in Client-Server Environment
- 86% of world commercial DB market in the Internet Environment
- Sybase (Good RDBMS)
- MS SQL Server
- Only works with Windows OS
- MS Access (Single User)
- DB2 (MainFrame Computer from IBM)
- Postgres (Open source)

_ Our Focus will be on MySQL _

MySQL

Launched by Swedish company in 1995.
MySQL is a open-source RDBMS (most widely used open-source RDBMS).
Part of *LAMP open-source web-development.
Sun Microsystems acquired MySQL in 2008
Oracle Corporation acquired Sun Microsystems in 2010.

L - Linux
A - Apache Web Server
M - MySQL
P - Perl, Python or PHP

Various S/W development tools from MySQL

MySQL Command Line Client (client S/W)
- Used for running SQL commands
- Character based (text based)
- Interface with database
MySQL Workbench (client S/W)
- Used for running SQL commands
- GUI based interface with database
MySQL Pl
- MySQL Programming Language
- Used for database Programming
MySQL Connectors
- for database connectivity (JDBC, ODBC, Python, C, C++ etc)
MySQL for Excel
- import, export, and edit MySQL data using MS Excel
MySQL Notifier
- Start-up and Shutdown the MySQL database
MySQL Enterprise Backup
- export and import of table data
- used to take backups and restore from the backups
MySQL Enterprise High Availability
- for replication (also know as data mirroring) concept of standby database
MySQL Enterprise Encryption
- used to encrypt table data
MySQL Enterprise Manager
- for performance monitoring, and performance tuning
MySQL Query Analyzer
- for query tuning

SQL

Structured Query Language
Commonly pronounced as "Sequel"
conforms to ANSI standards (e.g 1 character = 1 Byte)
Conforms to ISO standards (for QA)
Common for all RDBMS(hence also known as RDBMS)
Initially founded by IBM (1975-77)
Initially known as RQBE (Relational Query by Example)
IBM gave RQBE free of cost to ANSI
ANSI renamed RQBE to SQL (Now, controlled by ANSI6)
In 2005, source code of SQL was rewritten in Java(100%)

Every row in Table is a file, Table is not a file which helps us achieve row-level locking.

Divisions of SQL

DDL (Data Definition Language): Create, Drop, Alter
DML (Data Manipulation Language): Insert, Update, Delete
DCL (Data Control Language): Grant, Revoke
DQL (Data Query Language): Select

Extra in Oracle RDBMS and MySQL
1. DTL/TCL (Data Transaction Language)/(Transaction Control Language)
    Commit, Rollback, Savepoint
2. DDL
    Rename, Truncate

Extra in Oracle RDBMS only:
1. DML
    (Merge, Upsert(Update+Insert))

Rules for Table Names, columns names and variable names

Oracle: Max 30 characters MySQL: Max 64 characters
A-Z, a-z, 0-9 allowed
Has to begin with an alphabet
Special characters, $, #, _ allowed
In MySQL to use reserved characters such as # int tableName and column Name, enclose it in backquotes(`).EMP#`
134 reserved words not allowed

Under Linux & Unix, table_name and column_name are case-sensitive. But in Windows & macos are not case-sensitive