SQL进阶（2）——MySQL锁问题以及编码相关问题

最新推荐文章于 2024-11-09 21:51:58 发布

ICoder_Next

最新推荐文章于 2024-11-09 21:51:58 发布

阅读量131

点赞数

分类专栏：数据库文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_36079912/article/details/104524086

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

文章目录

1.mysql编码
2.数据库并发的控制（锁）
MySQL锁机制总结

1.mysql编码

1.1、查看mysql编码

mysql> show variables like 'character%';
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| Variable_name | Value |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+

1.2、设置mysql编码

# vi /etc/my.cnf
如下(少补)：
[mysqld]
character‐set‐server=utf8
collation‐server=utf8_general_ci
sql_mode='NO_ENGINE_SUBSTITUTION'
[mysql]
default‐character‐set = utf8
[mysql.server]
default‐character‐set = utf8
[mysqld_safe]
default‐character‐set = utf8
[client]
default‐character‐set = utf8
重启mysql
# service mysqld restart
# 或者使用 service mysql restart
再次查看编码：
# mysql ‐uroot ‐p
mysql> show variables like 'character%';
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| Variable_name | Value |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+
8 rows in set (0.00 sec)

1.3 mysql的目录及配置文件说明

A、/etc/my.cnf 这是mysql的主配置文件
B、/var/lib/mysql mysql数据库的数据库文件存放位置

[root@mysql etc]# cd /var/lib/mysql/
[root@mysql mysql]# ll
total 176172
‐rw‐rw‐‐‐‐. 1 mysql mysql 56 Oct 20 00:10 auto.cnf
‐rw‐rw‐‐‐‐. 1 mysql mysql 79691776 Oct 21 04:46 ibdata1
‐rw‐rw‐‐‐‐. 1 mysql mysql 50331648 Oct 21 04:46 ib_logfile0
‐rw‐rw‐‐‐‐. 1 mysql mysql 50331648 Oct 20 00:09 ib_logfile1
drwx‐‐‐‐‐‐. 2 mysql mysql 4096 Oct 21 01:38 itcast
drwx‐‐‐‐‐‐. 2 mysql mysql 4096 Oct 21 04:45 menagerie
drwx‐‐x‐‐x. 2 mysql mysql 4096 Oct 20 00:09 mysql
‐rw‐r‐‐‐‐‐. 1 mysql root 2219 Oct 20 00:14 mysql.err
‐rw‐rw‐‐‐‐. 1 mysql mysql 5 Oct 20 00:10 mysql.pid
srwxrwxrwx. 1 mysql mysql 0 Oct 20 00:10 mysql.sock
drwx‐‐‐‐‐‐. 2 mysql mysql 4096 Oct 20 00:09 performance_schema
‐rw‐r‐‐r‐‐. 1 root root 111 Oct 20 00:09 RPM_UPGRADE_HISTORY
‐rw‐r‐‐r‐‐. 1 mysql mysql 111 Oct 20 00:09 RPM_UPGRADE_MARKER‐LAST
drwxr‐xr‐x. 2 mysql mysql 4096 Oct 20 00:09 test

我们的mysql数据库的数据库文件通常是存放在了**/ver/lib/mysql**这个目录下。
C、/var/log/mysql数据库的日志输出存放位置
D、查看端口。Netstat –nltp 看是否能找到3306的端口

[root@mysql menagerie]# netstat ‐nltp
Active Internet connections (only servers)
Proto Recv‐Q Send‐Q Local Address Foreign Address
State PID/Program name
tcp 0 0 0.0.0.0:57958 0.0.0.0:*
LISTEN 1306/rpc.statd
tcp 0 0 0.0.0.0:111 0.0.0.0:*
LISTEN 1284/rpcbind
tcp 0 0 0.0.0.0:22 0.0.0.0:*
LISTEN 1573/sshd
tcp 0 0 127.0.0.1:631 0.0.0.0:*
LISTEN 1362/cupsd
tcp 0 0 127.0.0.1:25 0.0.0.0:*
LISTEN 1654/master
tcp 0 0 :::3306 :::*
LISTEN 37860/mysqld
tcp 0 0 :::49647 :::*
LISTEN 1306/rpc.statd
tcp 0 0 :::111 :::*
LISTEN 1284/rpcbind
tcp 0 0 :::22 :::*
LISTEN 1573/sshd
tcp 0 0 ::1:631 :::*
LISTEN 1362/cupsd
tcp 0 0 ::1:25 :::*
LISTEN 1654/master

[root@mysql mysql]# cat mysql.pid
37860

2.数据库并发的控制（锁）

2.1锁的概念：

首先我们先了解下什么是数据库锁，

锁是事务对某个数据库中的资源（如表和记录）存取前，先向系统提出请求，封锁该资源，事务获得锁后，即取得对数据的控制权，在事务释放它的锁之前，其他事务不能更新此数据。当事务撤消后，释放被锁定的资源。

2.2 为什么要锁？

数据库是一个多用户使用的共享资源,比如一个用户表 t_user,两个浏览器前面的人登录了同个一个账号，把电话号码改了。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性(脏读，不可重复读，幻读等)，可能产生死锁。为了解决这个问题，加锁是一个非常重要的技术，对实现数据库并发控制是一个好的方案。简单说，当一个执行sql语句的事务想要操作表记录之前，先向数据库发出请求，对你访问的记录集加锁，在这个事务释放这个锁之前，其他事务不能对这些数据进行更新操作。

2.3 数据库锁的分类：

1.乐观锁：

乐观锁不是数据库自带的，需要我们自己去实现。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。
通常实现是这样的：在表中的数据进行操作时(更新)，先给数据表加一个版本(version)字段，每操作一次，将那条记录的版本号加1。也就是先查询出那条记录，获取出version字段,如果要对那条记录进行操作(更新),则先判断此刻version的值是否与刚刚查询出来时的version的值相等，如果相等，则说明这段期间，没有其他程序对其进行操作，则可以执行更新，将version字段的值加1；如果更新时发现此刻的version值与刚刚获取出来的version的值不相等，则说明这段期间已经有其他程序对其进行操作了，则不进行更新操作。
举例：
下单操作包括3步骤：
1.查询出商品信息

select (status,price,version) from t_goods where id=#{id}

2.根据商品信息生成订单
3.修改商品status为2

update t_goods

set status=2,version=version+1

where id=#{id} and version=#{version};

2.悲观锁：

与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。
说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

共享锁：

又叫S锁或者读锁，加了共享锁的数据对象可以被其他事务读取，但不能修改，通常是该数据对象被读取完毕，锁立即被释放

排他锁：

又叫X锁或者写锁，当数据对象被加上排它锁时，一个事务必须得到锁才能对该数据对象进行访问，一直到事务结束锁才被释放。在此之间其他的事务不能对它读取和修改。

3.行锁：

行锁，由字面意思理解，就是给某一行加上锁，也就是一条记录加上锁。
比如之前演示的共享锁语句
SELECT * from city where id = “1” lock in share mode;
由于对于city表中,id字段为主键，就也相当于索引。执行加锁时，会将id这个索引为1的记录加上锁，那么这个锁就是行锁。