您的位置:新葡亰496net > 网络数据库 > 新葡亰496net:mysql中的表锁定及事务控制,事务控

新葡亰496net:mysql中的表锁定及事务控制,事务控

发布时间:2019-07-28 22:41编辑:网络数据库浏览(65)

    1.  隐含的执行unlock tables

    一、基本介绍

    start transaction, commit和rollback语法
    start transaction | begin [work]commit [work] [and [no] chain] [[no] release]rollback [work] [and [no] chain] [[no] release]set autocommit = {0 | 1}start transaction或begin语句可以开始一项新的事务。commit可以提交当前事务,是变更成为永久变更。rollback可以 回滚当前事务,取消其变更。set autocommit语句可以禁用或启用默认的autocommit模式,用于当前连接。

    概述

    MySQL支持三种锁级别:页级表级行级。MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。在默认情况下,表锁和行锁都是自动获取的,不需要额外的命令。三种锁级别优缺点如下:

    • 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
    • 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
    • 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

    原文链接: 数据库必须在Mysql服务下启动

      如果在锁表期间,用start transaction命令来开始一个新事务,会造成一个隐含的unlock tables 被执行,如下所示:

    事务就是一组dml语句,这些语句存在逻辑上的相关性,这一组dml语句要么全部成功,要么全部失败,是一个整体。ACID属性(原子性,一致性,隔离性,持久性);mysql提供一种机制,保证我们可以达到这样的效果。事务还规定了不同的客户端看到的数据是不相同的, 举例说明:

    自选的work关键词被支持,用于commit和release,与chain和release子句。chain和release可以被用于对事务完成进行附加控制。completion_type系统变量的值决定了默认完成的性质。请参见5.3.3节,“服务器系统变量”。

    表锁定

    在windows 下 启动Mysql cd到mysqlbin 目录下 在dos窗口下 启动关闭mysql服务

    会话1

    会话2

    SELECT * FROM country WHERE country='德国';

    记录为空

    SELECT * FROM country WHERE country='德国';

    记录为空

    --  对country表进行加 写锁

    LOCK TABLE country WRITE;

     

     

    -- 查询 整个表读取被阻塞

    SELECT * FROM country WHERE country='德国';

    -- 插入一条记录

    INSERT INTO country(country, last_update) VALUES('德国',NOW());

    共 1 行受到影响

    country_id        country    last_update

    2       中国         2018-07-03 18:06:45

    7       德国         2018-07-12 17:22:08

    查询等待

    -- 开始一个新事务

    START TRANSACTION;

     

     

    会话1开始一个新事务时,表锁被释放,可以查询:

    SELECT * FROM country;

    country_id        country    last_update

    2       中国         2018-07-03 18:06:45

    7       德国         2018-07-12 17:22:08

    新葡亰496net 1

    and chain子句会在当前事务结束时,立刻启动一个新事务,并且新事务与刚结束的事务有相同的隔离等级。release子句在终止了当前事务后,会让服务器断开与当前客户端的连接。包含no关键词可以抑制chain或release完成。如果completion_type系统变量被设置为一定的值,使连锁或释放完成可以默认进行,此时no关键词有用。

    1.语法

    #锁定表
    LOCK TABLES 
        tb_name1 [AS alias] {READ[LOCAL]|[LOW_PRIORITY] WRITE}
        tb_name2 [AS alias] {READ[LOCAL]|[LOW_PRIORITY] WRITE}
        ...
    #释放表锁定
    UNLOCK TABLES;
    
    • lock tables 可以锁定用于当前线程(会话)的表。如果被其他线程锁定,则当前线程会等待,直到可以获取所有锁定为止。
    • unlock tables释放当前线程(会话)获得的任何锁定。
    • read(读锁/共享锁):当表不存在 WRITE 写锁时 READ 读锁被执行,这该状态下,当前线程不可以修改(insert,update,delete),其他线程的修改操作进入列队,当当前线程释放锁,其他线程修改被执行。
    • read local:read local和read之间的区别是,read local允许在锁定被保持时,执行非冲突性INSERT语句(同时插入)。但是,如果您正打算在MySQL外面操作数据库文件,同时您保持锁定,则不能使用read local。对于InnoDB表,read local与read相同。
    • write(写锁/排它锁):除了当前用户被允许读取和修改被锁表外,其他用户的所有访问(读/写)被完全阻止。注意的是在当前线程当WRITE被执行的时候,即使之前加了READ没被取消,也会被取消。
    • low_priority write:降低优先级的write,默认write的优先级高于read.假如当前线程的low_priority write在列队里面,在未执行之前其他线程传送一条read,那么low_priority write继续等待.
    • InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁!
    • 并发插入:原则上数据表有一个读锁时,其它进程无法对此表进行更新操作,但在一定条件下,MyISAM表也支持查询和插入操作的并发进行。MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2。
    • 当concurrent_insert设置为0时,不允许并发插入。
    • 当concurrent_insert设置为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。
    • 当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。
    //启动mysql服务mysqld --console//关闭mysql服务mysqladmin -uroot shutdown
    

       在同一个事务中,最好不要操作不同存储引擎的表, 因为只有commit 和rollback 只能对事务类型的表进行提交和回滚。通常 提交的事务记录会到二进制的日志中, 但如果一个事务中包含非事务类型的表,那么回滚操作也会被记录到二进制日志中,以确保非事务类型表的更新可以被复制到从slave数据库中。

    新葡亰496net 2

    默认情况下,mysql教程采用autocommit模式运行。这意味着,当您执行一个用于更新(修改)表的语句之后,mysql立刻把更新存储到磁盘中。

    2.举例

    这是一个获取表锁及释放表锁的例子。其中session1和session2表示两个同时打开的session(连接/线程),表格中的每一行表示同一时刻两个session的运行情况,后面的例子也是同样的格式。

    新葡亰496net 3

    表锁举例

    SQL 主要语句可以划分为一下3类

    2.  savepoint

    说明:  

    如果您正在使用一个事务安全型的存储引擎(如innodb, bdb或ndb簇),则您可以使用以下语句禁用autocommit模式:

    事务控制

    MySQL通过set autocommit、start transaction、commit、rollback等语句支持本地事务。

    • DDL:数据定义语言,这些语句定义不同的数据段、数据库、表、列、索引等数据库对象。常用语句关键字主要包括create,drop,alter等
    • DML:数据操作语句,用于添加、删除、更新和查询数据库记录,并检查数据完整性。常用语句关键字主要包括 insert,delete,update和select等。
    • DCL数据控制语句,用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要 的语句包括关键字grant、revoke等

      在事务中可以通过定义 savepoint,指定回滚事务的一个部分,但是不能提交提交事务的一个部分, 对于复杂的应用,可以定义多个不同的savepoint 来适应不同的条件,回滚不同的savepoint。如果定义了相同名字的savepoint,则后面定义的savepoint会覆盖之前的定义。对于不再需要使用的savepoint,可以通过release savepoint 来删除savepoint。
      下面举例回滚事务的一个部分,通过定义savepoint来指定需要回滚的事务的位置。

      默认情况下,MySQL是自动提交的;

    set autocommit=0;通过把autocommit变量设置为零,禁用autocommit模式之后,您必须使用commit把变更存储到磁盘中,或着如果您想要忽略从事务开始进行以来做出的变更,使用rollback。

    1.语法

    START TRANSACTION |BEGIN [WORK]
    COMMIT [WORK][AND [NO] CHAIN] [[NO] RELEASE]
    ROLLBACK [WORK][AND [NO] CHAIN] [[NO] RELEASE]
    SET AUTOCOMMIT={0|1}
    
    • 默认情况下,MySQL是自动提交的,如果需要通过明确的Commit和Rollback来提交和回滚事务,那么就需要通过明确的事务控制命令来开始事务。
    • start transaction 或begin语句开始一项新的事务。
    • commit和rollback用来提交或者回滚事务。
    • chain和release子句分别用来定义在事务提交或者回滚之后的操作,chain会立即启动一个新事物,并且和刚才的事务具有相同的隔离级别,release则会断开和客户端的连接。
    • set autocommit可以修改当前连接的提交方式,如果设置了set autocommit=0,则设置之后的所有事务都需要通过明确的命令进行提交或者回滚。

    是对数据库内部的对象进行创建 、删除、修改等操作语言,它和DML语句最大的区别是DML只是对表内部数据操作,而不涉及表的定义、结构的修改,更不会涉及其他对象。DDL更多地由数据库管理员使用。

    会话1

    会话2

    SELECT * FROM country WHERE country='德国';

    结果为空

    SELECT * FROM country WHERE country='德国';

    结果为空

    -- 启动一个事务 插入一条记录

    START TRANSACTION ;

    INSERT INTO country(country, last_update) VALUES('德国',NOW());

     

    -- 可以查询到刚插入的记录

    SELECT * FROM country WHERE country='德国';

    country_id        country    last_update

    12     德国         2018-07-12 18:17:12

    SELECT * FROM country WHERE country='德国';

    结果为空

    --  定义savepoint 名为testsavepoint

    SAVEPOINT savepoint_test;

    -- 继续插入一条记录, 此时事务还会提交

    INSERT INTO country(country, last_update) VALUES('日本',NOW());

     

     

    SELECT * FROM country WHERE country='德国' or country='日本'

    结果为空

    SELECT * FROM country WHERE country='德国' or country='日本'

    country_id        country    last_update

    12     德国         2018-07-12 18:17:12

    13     日本         2018-07-12 18:20:33

     

     

    -- 回滚刚才的定义

    ROLLBACK TO SAVEPOINT savepoint_test;

     

    SELECT * FROM country WHERE country='德国' OR country='日本'

    country_id        country    last_update

    12     德国         2018-07-12 18:17:12

    SELECT * FROM country WHERE country='德国' or country='日本'

    结果为空

    -- 提交事务

    COMMIT;

     

    SELECT * FROM country WHERE country='德国' OR country='日本'

    country_id        country    last_update

    12     德国         2018-07-12 18:17:12

    SELECT * FROM country

    country_id        country    last_update

    2       中国         2018-07-03 18:06:45

    12     德国         2018-07-12 18:17:12

    start transaction 或 begin 语句开始一项新的事务

    如果您想要对于一个单一系列的语句禁用autocommit模式,则您可以使用start transaction语句:

    2.举例

    举例一
    使用start transaction和commit and chain。

    新葡亰496net 4

    事务控制1-1

    新葡亰496net 5

    事务控制1-2

    新葡亰496net 6

    事务控制1-3

    举例二
    在锁表期间,用start transaction命令开启新事务,会造成隐含的unlock tables被执行。

    新葡亰496net 7

    事务控制2-1

    新葡亰496net 8

    事务控制2-2

    举例三
    在事务中可以通过定义savepoint,指定回滚事务的一个部分,但是不能指定提交事务的一个部分。对于复杂的应用,可以定义多个不同的savepoint,满足不同的条件时,回滚不同的savepoint。需要注意的是,如果定义了相同名字的savepoint,则后面定义的savepoint会覆盖之前的定义。对于不再需要使用的savepoint,可以通过release savepoint命令删除savepoint,删除后的savepoint,不能再执行rollback to savepoint命令。

    新葡亰496net 9

    事务控制3-1

    新葡亰496net 10

    事务控制3-2

    连接mysql服务器 mysql -uroot -p 创建数据库test1create database test1;显示有哪些数据库show databases;//mysql 自动创建的表有information_schema:主要存储了系统中的一些数据库信息,比如用户表信息、列信息、权限信息、字符集信息、分区信息等等cluster:存储了系统的集群信息mysql:存储了系统的用户权限信息。test:系统自动创建的测试数据库,任何用户都可以访问选择数据库use test1显示test1数据库中创建的所有表show tables删除数据库drop database test1;创建表create table emp(ename varchar,hiredata date,sal decimal,deptno int;查看表定义desc emp;查看创建表的定义show create table emp;删除表drop table emp;修改表alter table emp modify ename varchar;增加表字段alter table emp add column age int;删除表字段alter table emp drop column age;字段改名alter table emp change age age1 int;change 和modify都可以修改表的定义,不同的是change后面需要写两次列名,不方便,但是change的优点是可以修改列名称,则modify则不能修改字段排序alter table emp add birth date after ename;alter table emp modify age int first;更改表名alter table emp rename emp1;
    

    commit 和 rollback 用来提交或回滚事务

    start transaction;select @a:=sum(salary) from table1 where type=1;update table2 set [email protected] where type=1;commit;使用start transaction,autocommit仍然被禁用,直到您使用commit或rollback结束事务为止。然后autocommit模式恢复到原来的状态。

    参考

    http://blog.csdn.net/xiao7ng/article/details/4916220
    http://www.cnblogs.com/huangye-dream/archive/2013/07/06/3174725.html
    http://c.biancheng.net/cpp/html/1469.html

    DML语句是指对数据库中表记录的操作,主要包括表记录的插入、更新、删除和查询。

    chainrelease子句分别用来定义在事务提交或者回滚之后的操作,chain会立即启动一个新事务,并且和刚才的事务具有相同的隔离级别,release则会断开和客户端的链接;语句 commit and chain; 提交后立即开始一个新的事务

    begin和begin work被作为start transaction的别名受到支持,用于对事务进行初始化。start transaction是标准的sql语法,并且是启动一个ad-hoc事务的推荐方法。begin语句与begin关键词的使用不同。begin关键词可以启动一个begin...end复合语句。后者不会开始一项事务。请参见20.2.7节,“begin ... end复合语句”。

    插入记录insert into emp(ename,hiredate,sal,deptno)values('zzx1','2000-01-01','2000',1);也可以不用指定字段名称,但是values后面的顺序要和字段的排列顺序一致inset into emp('zzx1','2000-01-01','2000',1);含可空字段、非空但是含有默认值的字段、自增字段、可以不用再insert后的字段列表里面出现,values后面只写对应字段名称的value,没写的字段可以自动设置为null、默认值、自增的下一个数字批量增加用逗号隔开insert into dept values,;更新记录update emp set sal=4000 where ename='xxx';删除记录delete from emp where ename='doney';查询记录select * from emp;*表示所有记录,也可以用逗号隔开的字段来选择查询查询不重复的记录select distinct deptno from emp;条件查询用where关键字来实现,可以使用<>!=等多条件可以使用or、and等排序和限制desc和asc是排序关键字,desc是降序、asc是升序排列 ORDER BY 排序,默认是升序select * from emp order by sal;如果排序字段的值一样,则值相同的字段按照第二个排序字段进行排序,如果只有一个排序字段,则相同字段将会无序排序select * from emp order by deptno,sal desc;限制select * from emp order by sal limit 3;//前者是起始偏移量,后者是显示行数select * from emp order by sal limit 1,3;limit 和order by 一起使用来做分页聚合用户做一下些汇总操作sum,count,max,minwith rollup 是可选语法,表示是否对分类聚合后的结果进行再汇总having 表示对分类后的结果在进行条件的过滤。select deptno,count from emp group by deptno having count>=1;
    

    set autocommit 可以修改当前链接的提交方式,如果设置了set autocommit=0,则设置之后的所有事务都需要通过明确的命令进行提交或回滚

    您也可以按照如下方法开始一项事务:

    大类上分为外连接和内连接外链接 又分为左连接和右连接

    默认情况下只对提交的事务记录到二进制的日志中。

    start transaction with consistent snaps教程hot;with consistent snapshot子句用于启动一个一致的读取,用于具有此类功能的存储引擎。目前,该子句只适用于innodb。该子句的效果与发布一个start transaction,后面跟一个来自任何innodb表的select的效果一样。请参见15.2.10.4节,“一致的非锁定读”。

    左连接:包含所以的左边表中的记录甚至是右边表中没有和它匹配的记录。右连接:同上

    所有的DDL语句是不能回滚的,并且部分的DDL语句会造成隐式的提交。

    开始一项事务会造成一个隐含的unlock tables被执行。

    select ename,detname from emp left join dept on emp.deptno=dept.deptno;左连接和右连接可以相互转换
    

       默认情况下,表锁和行锁都是自动获得的,不需要额外的命令。

    为了获得最好的结果,事务应只使用由单一事务存储引擎管理的表执行。否则,会出现以下问题:

    子查询

    lock tables 表名 [read|write] ; 锁定当前线程的表,若表被其他线程锁定,则当前线程会等待;

    ·         如果您使用的表来自多个事务安全型存储引擎(例如innodb和bdb),并且事务隔离等级不是serializable,则有可能当一个事务提交时,其它正在进行中的、使用同样的表的事务将只会发生由第一个事务产生的变更。也就是,用混合引擎不能保证事务的原子性,并会造成不一致。(如果混合引擎事务不经常有,则您可以根据需要使用set transaction isolation level把隔离等级设置到serializable。)

    select * from emp where deptno in(select deptno from dept);如果子查询记录唯一,可以使用=替代inselect * from emp where deptno =(select deptno from dept limit 1);
    

    unlock tables 释放当前线程获得的任何锁定

    ·         如果您在事务中使用非事务安全型表,则对这些表的任何变更被立刻存储,不论autocommit模式的状态如何。

    记录查询将两个表的数据按照一定的查询出来后,将结果合在一起显示

    在所锁表期间,用start transaction命令开始一个新事务,会造成一个隐含的unlock tables被执行。

    如果您在更新了事务中一个事务表之后,发布一个rollback语句,则会出现一个er_warning_not_complete_rollback警告。对事务安全型表的变更被 回滚,但是对非事务安全型表没有变更。

    union all 是将结果集合并在一起,而union是将union all后的结果在进行一次distinct,去除重复

    二、如何在mysql控制台使用事务

    每个事务被存储在一个组块中的二进制日志中,在commit之上。被回滚的事务不被计入日志。(例外情况:对非事务表的更改不会被 回滚。如果一个被回滚的事务包括对非事务表的更改,则整个事务使用一个在末端的rollback语句计入日志,以确保对这些表的更改进行复制。)见5.11.3节,“二进制日志”。

    select deptno from emp union all select deptno from dept;select demtno from emp union select deptno from dept;
    

    1、创建一张表

    您可以使用set transaction isolation level更改事务的隔离等级。请参见13.4.6节,“set transaction语法”。

    ? xxx 来查看

    2、事务的基本使用

    回滚可以慢速运行。在用户没有明确要求时,也可以进行回滚(例如,当错误发生时)。因此,在明确地和隐含的(rollback sql命令)回滚时,show processlist会在stage列中显示rolling back,用于连接。

    如果要查看类别 ? data types 具体的 ? int 查看语法 如 ? create table
    

      开始一个事务

    13.4.2. 不能回滚的语句
    有些语句不能被回滚。通常,这些语句包括数据定义语言(ddl)语句,比如创建或取消数据库教程的语句,和创建、取消或更改表或存储的子程序的语句。

    数据类型

          start transaction;

    您在设计事务时,不应包含这类语句。如果您在事务的前部中发布了一个不能被回滚的语句,则后部的其它语句会发生错误,在这些情况下,通过发布rollback语句不能 回滚事务的全部效果。

    对于整形数据,MySql还支持在类型名称后面的小括号设置宽度,默认设置为int,配合zerofill,当数字位数不够的时候,用字符‘0’填充alter table t1 modify id1 int zerofill
    

      做了一个保存点

    13.4.3. 会造成隐式提交的语句
    以下语句(以及同义词)均隐含地结束一个事务,似乎是在执行本语句前,您已经进行了一个commit。

    对于小数,MySql 分为两种,浮点数和定点数。浮点数包括float和double,而定点数只有decimal,定点数在Mysql内部是以字符串形式存放,比浮点数更精确,适合用于货币等精度高的数据

          savepoint 保存点名;

    ·         alter function, alter procedure, alter table, begin, create database, create function, create index, create procedure, create table, drop database, drop function, drop index, drop procedure, drop table, load master data, lock tables, rename table, set autocommit=1, start transaction, truncate table, unlock tables.

    浮点数和定点数可以用类型名称加 M是几位,D是位于小数点后面几位。

      这里进行了各种操作[update, delete , insert ......]

    ·         当当前所有的表均被锁定时,unlock tables可以提交事务。

    日期类型

      如果需要可以回退到保存点

    ·         create table, create database drop database, truncate table, alter function, alter procedure, create function, create procedure, drop function和drop procedure等语句会导致一个隐含提交。

    • DATE来表示年月日
    • DATETIME来表示年月日时分秒
    • TIME来表示时分秒
    • 当前系统时间,通常用TIMESTAMP来表示

          rollback to 保存点名;

    ·         innodb中的create table语句被作为一个单一事务进行处理。这意味着,来自用户的rollback不会撤销用户在事务处理过程中创建的create table语句。

    TIMESTAMP创建一个字段为TIMESTAMP类型,系统自动创建了默认值为CURRENT_TIMESTAMP。同时MySql规定TIMESTAMP类型字段一列只能有一个默认值current_timestamp。如果修改会报错.

    事务使用的注意事项:

    事务不能被嵌套。这是隐含commit的结果。当您发布一个start transaction语句或其同义词时,该commit被执行,用于任何当前事务。

    TIMESTAMP 另一个重要特点是与时区有关。当插入时间时,先转换为本地时区后存放,而从数据库取出时,同样会将日期转换为本地时区后显示,这样两个时区的用户看到同一个时区可能就不一样

      当我们开始事务时,没有做保存点,我们也可以回退到事务开始的哪个时候的状态(前提是,这个事务还没有提交commit)。

    13.4.4. savepoint和rollback to savepoint语法
    savepoint identifierrollback [work] to savepoint identifierrelease savepoint identifierinnodb支持sql语句savepoint, rollback to savepoint, release savepoint和自选的用于rollback的work关键词。

    查看当前时区show variables like 'time_zone';修改时区set time_zone=' 9.00';
    

      新葡亰496net 11

    savepoint语句用于设置一个事务保存点,带一个标识符名称。如果当前事务有一个同样名称的保存点,则旧的保存点被删除,新的保存点被设置。

    DATETIME插入的格式

      当回退时,数据全部恢复到事务开始的时候.

    rollback to savepoint语句会向以命名的保存点回滚一个事务。如果在保存点被设置后,当前事务对行进行了更改,则这些更改会在 回滚中被撤销。但是,innodb不会释放被存储在保存点之后的存储器中的行锁定。(注意,对于新插入的行,锁定信息被存储在行中的事务id承载;锁定没有被分开存储在存储器中。在这种情况下,行锁定在撤销中被释放。)在被命名的保存点之后设置的保存点被删除。

    YYYY-MM-DD HH:MM:SS 或YY-MM-DD HH:MM:SS 的字符串允许任何标点符号用来做时间部分的间隔符如92@12@31 11^30^45YYYYMMDDHHMMSS 或YYMMDDHHMMSS的格式没有间隔符的字符串
    

    如果一个事务被提交了,则不可以再回退

    如果语句返回以下错误,则意味着不存在带有指定名称的保存点:

    字符串类型

      新葡亰496net 12

    error 1181: got error 153 during rollbackrelease savepoint语句会从当前事务的一组保存点中删除已命名的保存点。不出现提交或 回滚。如果保存点不存在,会出现错误。

    CHAR和VARCHAR类型两者的主要区别是存储方式不同:CHAR列的长度固定为创建表时声明的长度,长度可以为0-255;二VARCHAR列中的值是可变长度。同时在检索的时候,CHAR列删除尾部的空格 ,而VARCHAR保留空格,由于CHAR是固定长度,所以它的处理速度别VARCHAR快很多,但是其缺点是浪费内存,在使用中VARCHAR被更多的使用

    我们事务中,可以有多个保存点,在回退的时候,可以选择回退到哪个保存点,同名后面的覆盖之前的定义。对于不需要的savepoint,通过release savepoint xxx;命令删除savepoint

    如果您执行commit或执行不能命名保存点的rollback,则当前事务的所有保存点被删除。

    create table vc (v varchar,c charinsert into vc values('ab ','ab ');selelct length,length from vc//4,2
    

    innodb支持事务,而myisam不支持.

    13.4.5. lock tables和unlock tables语法
    lock tables    tbl_name [as alias] {read [local] | [low_priority] write}    [, tbl_name [as alias] {read [local] | [low_priority] write}] ...unlock tableslock tables可以锁定用于当前线程的表。如果表被其它线程锁定,则造成堵塞,直到可以获取所有锁定为止。unlock tables可以释放被当前线程保持的任何锁定。当线程发布另一个lock tables时,或当与服务器的连接被关闭时,所有由当前线程锁定的表被隐含地解锁。

    枚举

    开始事务可以是 start transaction 也可以是 set autocommit= 0; 或者 begin;

      表锁定只用于防止其它客户端进行不正当地读取和写入。保持锁定(即使是读取锁定)的客户端可以进行表层级的操作,比如drop table。

    create table t(gender enum;insert into t values,,,//m,f,f,null
    

    Start transaction 开始事务,事务结束后自动回到自动提交方式,即set autocommit =1;

    注意,下面是对事务表使用lock tables的说明:

    set类型set类型可以一次选取多个成员

    Start transaction 命令开启一个事务,会造成一个隐含的unlock tables被执行;

    ·         在尝试锁定表之前,lock tables不是事务安全型的,会隐含地提交所有活性事务。同时,开始一项事务(例如,使用start transaction),会隐含地执行unlock tables。(见13.4.3节,“会造成隐式提交的语句”。

    create table t2 (col set('a','b','c','d'));INSERT into t2 VALUE ,,,,;对于这个包含重复成员的集合只取一次 结果为’a,d'
    

    新葡亰496net:mysql中的表锁定及事务控制,事务控制和锁定语句。所有的DDL语句不能回滚,并且部分的DDL语句会造成隐式提交

    ·         对事务表(如innodb)使用lock tables的正确方法是,设置autocommit=0并且不能调用unlock tables,直到您明确地提交事务为止。当您调用lock tables时,innodb会内部地取其自己的表锁定,mysql取其自己的表锁定。innodb在下一个提交时释放其表锁定,但是,对于mysql,要释放表锁定,您必须调用unlock tables。您不应该让autocommit=1,因为那样的话,innodb会在调用lock tables之后立刻释放表锁定,并且很容易形成死锁定。注意,如果autocommit=1,我们根本不能获取innodb表锁定,这样就可以帮助旧的应用软件避免不必要的死锁定。

    运算符

    通常情况下,只对提交的事务记录到二进制的日志中,但如果一个事务包含非事务类型的表,那么回滚操作也会被记录到二进制日志中,以确保非事务类型表的更新可以被复制到从数据库;

    ·         rollback不会释放mysql的非事务表锁定。

    DIV==/==除法获取商MOD==%==除法获取余数
    

    分布式事务的使用

    要使用lock tables,您必须拥有相关表的lock tables权限和select权限。

    不能用于null比较,后者可以

    三、如何在php程序中控制事务

    使用lock tables的主要原因是仿效事务,或在更新表时加快速度。这将在后面进行更详细的解释。

    between 使用格式 a between min and max 等价于 a>=min and a<=maxin的使用格式 a in(value1,value2...);like 使用格式如 a like 3%,当字符串含有123则返回1 否则返回0REGEXP 使用格式 str REGEXP str_pat 当str字符串中含有str_pat 相匹配的字符串,则返回1
    

    在没有事务的时候,代码是如此这般

    如果一个线程获得对一个表地read锁定,该线程(和所有其它线程)只能从该表中读取。如果一个线程获得对一个表的write锁定,只有保持锁定的线程可以对表进行写入。其它的线程被阻止,直到锁定被释放时为止。

    位运算

    新葡亰496net 13

    read local和read之间的区别是,read local允许在锁定被保持时,执行非冲突性insert语句(同时插入)。但是,如果您正打算在mysql外面操作数据库文件,同时您保持锁定,则不能使用read local。对于innodb表,read local与read相同。

    运算符 作用
    & and
    | or
    ^ xor
    ~ 位异或
    >> 位右移
    << 位左移

    使用事务来控制这个问题.

    当您使用lock tables时,您必须锁定您打算在查询中使用的所有的表。虽然使用lock tables语句获得的锁定仍然有效,但是您不能访问没有被此语句锁定的任何的表。同时,您不能在一次查询中多次使用一个已锁定的表——使用别名代替,在此情况下,您必须分别获得对每个别名的锁定。

    常用函数

    新葡亰496net 14

    mysql> lock table t write, t as t1 write;mysql> insert into t select * from t;error 1100: table 't' was not locked with lock tablesmysql> insert into t select * from t as t1;如果您的查询使用一个别名引用一个表,那么您必须使用同样的别名锁定该表。如果没有指定别名,则不会锁定该表。

    函数 功能
    CONCAT(s1,s2,s3...) 连接s1到sn的字符串(任何字符串和null拼接都是null)
    insert(str,x,y,instr) 将字符串str从x位置开始,y字符长的子串替换为字符串instr
    lower 将字符串str中所有字符变为小写
    UPPER 大写
    LEFT 返回字符串str最左边x个字符
    RIGHT 返回字符串str最右边的x个字符
    LPAD(str,n,pad) 用字符串pad对str最左边进行填充,直到长度为n个字符串长度
    PRPAD(str,n,pad) 用字符串pad对str最右边进行填充,直到长度为n个字符串长度
    LTRIM 去掉字符串str左侧的空格
    RIGHT 去掉字符串str行尾的空格
    REPEAT 返回str重复x次的结果
    REPLACE 用字符串b替换字符串str中所有出现的字符串a
    STRCMP 比较字符串s1和s2
    TRIM 去掉行尾和行头的空格
    SUBSTRING 返回字符串str x位置起y字符串长度的字串

    新葡亰496net 15

    mysql> lock table t read;mysql> select * from t as myalias;error 1100: table 'myalias' was not locked with lock tables相反的,如果您使用一个别名锁定一个表,您必须使用该别名在您的查询中引用该表。

    数字函数

    mysql> lock table t as myalias read;mysql> select * from t;error 1100: table 't' was not locked with lock tablesmysql> select * from t as myalias;write锁定通常比read锁定拥有更高的优先权,以确保更新被尽快地处理。这意味着,如果一个线程获得了一个read锁定,则另一个线程会申请一个write锁定,后续的read锁定申请会等待,直到write线程获得锁定并释放锁定。您可以使用low_新葡亰496net:mysql中的表锁定及事务控制,事务控制和锁定语句。priority write锁定来允许其它线程在该线程正在等待write锁定时获得read锁定。只有当您确定最终将有一个时机,此时没有线程拥有read锁定时,您才应该使用low_priority write锁定。

    函数 功能
    ABS 返回x的绝对值
    CEIL 返回大于x的最小整数值
    FLOOR 返回小于x的最大整数值
    MOD 返回x/y的模
    RAND() 返回0-1内的随机值
    ROUND 返回参数x的四舍五入的有y位小数的值
    TRUNCATE 返回数值x截断为y位小树的结果

    lock tables按照如下方式执行:

    日期和时间函数

    1.    按照内部定义的顺序,对所有要被锁定的表进行分类。从用户的角度,此顺序是未经定义的。

    函数 功能
    CURDATE() 返回当前日期
    CURTIME() 返回当前时间
    NOW() 返回当前的日期和时间
    UNIX_TIMESTAMP 返回date的unix时间戳
    FROM_UNIXTIME 返回UNIX时间戳的日期值
    WEEK 返回日期date为一年中的第几周
    YEAR 返回日期date的年份
    HOUR 返回time的小时值
    MINUTE 返回time的分钟值
    MONTHNAME 返回date的月份名
    DATE_FROMATE 返回按字符串fmt格式化日期date值
    DATE_ADD(date,interval expr type) 返回一个日期或时间值加上一个时间间隔的时间值
    DATEDIFF(expr,expr2) 返回起始时间expr和结束时间expr2之间的天数

    2.    如果使用一个读取和一个写入锁定对一个表进行锁定,则把写入锁定放在读取锁定之前。

    流程函数

    3.    一次锁定一个表,直到线程得到所有锁定为止。

    函数 功能
    IF(value,t f) 如果value是真 返回 t;否则返回f
    IFNULL(value1,value2) 如果value1不为空,返回value1,负责返回value2
    CASE WHEN[value1] THEN[value2]...ELSE[default] END 如果value1是真,返回result1否则返回defalut
    case [expr] WHEN[value1] THEN[value2]...ELSE[default] END 如果expr等于value1,返回result1否则返回defalut

    该规则确保表锁定不会出现死锁定。但是,对于该规则,您需要注意其它的事情:

    实例

    如果您正在对一个表使用一个low_priority write锁定,这只意味着,mysql等待特定的锁定,直到没有申请read锁定的线程时为止。当线程已经获得write锁定,并正在等待得到锁定表清单中的用于下一个表的锁定时,所有其它线程会等待write锁定被释放。如果这成为对于应用程序的严重的问题,则您应该考虑把部分表转化为事务安全型表。

    create table salary(userid int ,salary decimal;insert into salary values,,,,,;select * from salaryselect if(salary>2000,'high','low') from salary;select ifnull from salary;select case when salary <=2000 then 'low' else 'high' end from salary;select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary; 
    

    您可以安全地使用kill来结束一个正在等待表锁定的线程。请参见13.5.5.3节,“kill语法”。

    其他函数

    注意,您不能使用insert delayed锁定任何您正在使用的表,因为,在这种情况下,insert由另一个线程执行。

    函数 功能
    DATABASE() 返回的确数据库库名
    VERSION() 返回当前数据库版本
    USER() 返回当前登录用户名
    INET_ATON 返回ip地址的数字表示
    INET_NTOA 返回数字代表的ip地址
    PASSWORD 返回字符串str加密版本
    MD5() 返回字符串的md5值

    通常,您不需要锁定表,因为所有的单个update语句都是原子性的;没有其它的线程可以干扰任何其它当前正在执行的sql语句。但是,在几种情况下,锁定表会有好处:

    MySql引擎MySql支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等,其中InnoDB和BDB提供事务安全表,用户可以选择不同的数据存储引擎来提高应用的效率

    ·         如果您正在对一组myisam表运行许多操作,锁定您正在使用的表,可以快很多。锁定myisam表可以加快插入、更新或删除的速度。不利方面是,没有线程可以更新一个用read锁定的表(包括保持锁定的表),也没有线程可以访问用write锁定的表(除了保持锁定的表以外)。

    创建表如果不指定存储引擎,系统默认使用默认存储引擎,MySql5.5之前的默认引擎是MyISAM,5.5之后改为InnoDB。如果要修改默认的存储引擎,可以在参数文件中设置default-table-type.

    有些myisam操作在lock tables之下更快的原因是,mysql不会清空用于已锁定表的关键缓存,直到unlock table被调用为止。通常,关键缓存在每个sql语句之后被清空。

    show ENGINES //查看的确支持的存储引擎//通过增加engine关键字设置新建表的储存引擎zcreate table ai(i bigint not null auto_increment,primary keyengine=innodb default charset=gbk;通过alter 来修改一个表的存储引擎ALTER TABLE ai ENGINE =MyISAM;
    

    ·         如果您正在使用mysql中的一个不支持事务的存储引擎,则如果您想要确定在select和update之间没有其它线程,您必须使用lock tables。本处所示的例子要求lock tables,以便安全地执行:

    MyISAMMyISAM 不支持事务、也不 不支持外键,其优点是速度快,对事务完整性没有要求。以SELECT和INSERT为主的应用基本上都就可以使用这个表

    ·                mysql> lock tables trans read, customer write;·                mysql> select sum(value) from trans where customer_id=some_id;·                mysql> update customer·                    ->     set total_value=sum_from_previous_statement·                    ->     where customer_id=some_id;·                mysql> unlock tables;如果没有lock tables,有可能另一个线程会在执行select和update语句之间在trans表中插入一个新行

    InnoDBInnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎,InnoDB写的处理效率差一些,并且会占用更多的磁盘空间以保留数据和索引。

    transaction, commit和rollback语法 start transaction | begin [work]commit [work] [and [no] chain] [[no] release]rollback [work] [and [no] chain] [[no] release]set autocommi...

    create table autoincre_demo (i smallint not null auto_increment,name varchar,primary keyengine=innodb;insert into autoincre_demo values,, 如果插入空或者0,则实际插入的将是自动增长后的值。可以通过以下语句强制设置自动增加列的初始值,默认从1开始,但是该强制的默认值是保留到内存中,如果数据库从起,这个强制的默认值会丢失,就需要数据库启动后重新设置ALTER TABLE *** auto_increment =n 
    

    MEMORYmemory 存储引擎使用存在于内存中的内容来创建表,每个MEMORY表实际对应一个磁盘文件,格式是.fm,MEMORY表的访问非常快,因为它的数据是放在内存中,并且默认使用HASH索引,但是一旦服务关闭,表中的数据就会

    alter table t2 engine=memory;show TABLE status like 't2'给memory表创建索引。可以指定hash索引还是btree索引create index mem_hash using hash on tab_memory;
    

    在启动MySql服务的时候使用--init-file选项,把INSERT INTO ... SELECT或LOAD DATA INFILE这样的语句放入这个文件中,就可以在服务启动时从持久稳固的数据源装载表服务器需要足够的内存来维持同一时间使用的MEMORY表,当不需要MEMORY表的内容,要释放MEMORY表的内存,执行DELETE FROM或 TRUNCATE TABLE 或者是DROP TABLE每个MEMORY表中可以放置的数据量的大小,受max_heap_table_size系统变量的约束,初始值是16mb,可以根据需要加大、MEMORY类型的存储引擎主要用在那些内容变化不平凡的表,或作为统计操作的中间结果表,便于高效的对中间结果进行分析并得到最终的统计结果。

    TokuDBTokuDB是第三方的存储引擎,是一个高性能、支持事务处理的MySql和MariaDB的存储引擎,具有高扩展性、高压缩、高效率的写入性能,支持大多数在线的DDL操作TokuDB 特别适用的场景

    • 日志数据,因为日志数据通常插入频繁且储存量大
    • 历史数据,通常不会有在写的操作,可以利用TokuDB的高压缩特性进行存储
    • 在线DDL频繁的场景
    • MyISAM:如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性,并发性要求不高,那么选择这个引擎非常合适
    • Innodb:用于事务的处理,支持外键。如果应用对事务的完整性较高的要求,在并发条件下要求数据的一致性,数据除了插入和查询外,还包括很多的更新和删除操作,那么Innodb存储引擎比较适合
    • MEMORY:将所有数据都存在RAM中,如果需要快速定位记录和其他类似数据的环境下,可以提供极快的访问,缺陷在于对表大小的限制,太大的表无法缓存在内存中,其次是要确保表的数据是可恢复的.
    • MERGE:用于将一系列等同MyISAM表以逻辑方式组合在一起,并作为一个对象引用它们。MERGE表的优点在于可以突破单个MyISAM表大小的限制,并且通过将不同的表分布在多个磁盘上,可以有效的改善MERGE表的访问效率

    Text与BLOB如果保存少量字符串会选择CHAR和VARCHAR 但是保存较大文本时,选择text或blob,两者主要差别是blob能用来保存二进制数据如图片;而text只能保存字符数据

    BLOB与TEXT引起的性能问题,特别是在执行大量的删除数据时,删除操作会留下很大的空洞,以后填入这些空洞的记录在插入的性能上会有影响,建立定期使用OPTIMIZE TABLE对这类表进行碎屏整理

    optimize table t
    

    使用合成的索引来提供大文本字段的查询性能

    合成索引就是根据大文本字段的内容建立一个散列值,并把值存储在单独的数据列中,接下来就是通过检索散列值找到数据行,但是只能做到精确匹配不能使用范围搜索。可以使用MD5,SHA1,CRC32 等生成散列值,使用精确匹配,在一定程度上减少了I/O,提高了查询效率。如果散列算法生成的字符串带有尾部空格,就不要存储在CHAR或VARCHAR列中,它会受尾部空格的影响

    如果需要对BLOB或CLOB字段进行模糊查询,MySQL提高前缀索引,也就是只为字段的前n列创建索引desc select * from t where context like 'beijing%' G;
    

    注意事项

    • 在不必要的时候避免检索大型的BLOB或TEXT:如SELECT * 查询,尽量从符合条件的数据行中检索BLOB或TEXT指
    • 把BLOB或TEXT列分离到单独表中:在某些环境下,如果把这些数据列移动到第二张数据表中,可以把原数据表中的数据列转换为固定长度的数据行格式,减少主表的碎片,可以得到固定长度数据行的性能优势。还可以在运行SELECT * 查询的时候不会通过网络传输大量的BLOB或TEXT指

    设计索引的原则

    • 搜索的索引列,不一定是所要选择的列。最适合索引的列是出现在where字句中的列,或连接字句中指定的列,而不是出现在select关键字后的列表中的列
    • 使用唯一索引.考虑到某列中的值分布,索引的列基础越大,索引的效果越好。入存放出生日期的列具有各部相同的值,很容易区分,但是记录性别的列,只含有男和女对此类进行索引没有多大好处
    • 使用短检索。如果对字符串进行检索,应该指定一个前缀长度。例如:一个CHAR列,如果前10个或20个字符内,多数值是唯一的,那么就不要对整个列进行检索。对前10个或20个字符进行检索能够节省大量索引空间,是查询更快。
    • 利用最左前缀。在创建一个n列索引时,实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用,因为可利用索引最左边的列级来匹配。
    • 不要过度索引。每个索引都是占用额外的磁盘空间,并降低写操作的性能。在修改表内容的时候,索引必须进行相应的更新,有时候需要重构。如果有一个索引很少被用到,那么会不必要的减缓表的修改速度。此外,mysql在生成一个执行计划时,要考虑各个索引,这也要花费时间。创建多余的索引给查询优化带来了更多的工作
    • 对于Innodb,记录默认会按照一定的顺序排序,如果有明确的定义主键,则按照主键排序顺序保存。
    什么是存储过程和函数

    存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程和函数可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提供数据处理的效率是有好处的。

    存储过程很函数的区别在于函数必须有返回值,而存储过程没有,储存过程的参数可以使用IN,OUT,INOUT类型,而函数的参数只能是IN类型的。如果有函数从其他类型的数据库迁移到MySQL,那么就可能因此需要将函数改造成存储过程。

    存储过程和函数的相关操作

    在对储存过程和函数操作时,需要首先确认用户是否具有相应的权限。例如,创建存储过程或者函数需要CREATE ROUTINE权限,修改或者删除存储过程或者函数需要ALTER ROUTINE权限,执行过程或者函数需要EXECUTE权限

    创建一个新的过程 film_in_stock,该过程用来检查 film_id和store_id对应的inventory是否满足要求,并且返回满足的inventory_id 以及满足要求的记录数CREATE PROCEDURE film_in_stock(in p_fim_id int,in p_store_id int,out p_film_count int)READS sql databegin select inventory_id from inventory where film_id =p_film_id and store_id=p_store_id and inventory_in_stock(inventory_id); SELECT found_rows() into p_film_count;end $$通常在创建过程和函数之前,都会通过DELIMITE $$命令将语句的结束符从';'修改成其他符号,这里使用‘$$’,这样在过程和函数中的';'就不会被MySql,解释成语句的结束而错误。在存储过程或者函数创建完成 通过‘DELIMITER;'命令在将结束符改回成';'调用过程CALL film_in_stock;存储过程的好处在于处理逻辑都封装在数据库端,调用者不需要了解中间的处理逻辑,一旦逻辑改变,只需要修改存储过程,对调用者的程序没有影响删除存储过程或者函数一次只能删除一个存储过程或者函数,删除需要ALTER ROUTINE权限drop procedure film_in_stock;查看存储过程或者函数状态show procedure status like 'film_in_stock';查看存储过程的函数定义show create procedure film_in_stock变量使用存储过程和函数中可以使用变量,在MySql 5.1版本中,变量不区分大小写变量的定义通过DECLARE可以定义一个局部变量,该变量的作用范围只能在BEGIN...END中,可以用在嵌套块中定义一个DATE类型的变量DECLARE last_month_start date;变量赋值 可以直接赋值,或者通过查询赋值。直接赋值使用set,可以赋常量或者赋表达式set var_name=expr [,var_name=expr]...set last_month_start=date_sub(current_date(),interval month);select col_name[,...] into var_name[,...] table_expr;
    

    定义条件和处理

    delimiter $$create procedure actor_insert()begin declare continue handler for sqlstate '23000' set @x2=1; set @x=1; insert into actor(actor_id,first_name,last_name) values(201,'test','201'); set @x=2; insert into actor(actor_id,first_name,last_name) values(1,'test','1'); set @x=3;end ;$$调用处理函数时遇到主键重的错误会按照定义的处理方式去处理,由于定义的是CONTINUE 会继续执行下面的语句还支持EXIT表示终止
    

    光标使用

    声明光标declare cursor_name cursor for select_statementopen光标open cursor_namefetch光标fetch cursor_name into var_name[,var_name]...close光标close cursor_namedelimiter $$create procedure payment_stat()begin declare i_staff_id int; declare d_amount decimal; declare cur_payment cursor for select staff_id,amount from payment; declare exit handler for not found close cur_payment; set @x1=0; set @x2=0; open cur_payment; REPEAT FETCH cur_payment into i_staff_id,d_amount; if i_staff_id =2 then set @x1=@x1 d_amount; else set @x2=@x2 d_amount; end if; until 0 end repeat; close cur_payment; end; $$变量,条件,处理程序,光标都是通过DECLARE定义的,她们之间是有先后顺序要求的。变量和条件必须在最前面声明,然后才能是光标的声明,最后才可以是处理程序的声明 
    

    控制语句

    case when i_staff_id =2 then set @x1=@x1 d_amount; else set @x2=@x2 d_amount;loop 和leave结合create procedure actor_insert()begin set @x=0; ins:loop set @x=@x 1; if @x=100 then leave ins; end if; insert into actor(first_name,last_name) values('Test','201'); end loop ins;end;$$inerate 语句作用是跳过当前循环的剩下语句,直接进入下一轮循环create procedure actor_insert()begin set @x=0; ins:loop set @x=@x 1; if @x=10 then leave ins; elseif mod=0 then iterate ins; end if; insert into actor(actor_id,first_name,last_name) values(@x 200,'test',@x); end loop ins;end;$$repeat 语句 有条件的循环控制语句,当满足条件的时候退出循环repeat fetch cur_payment into i_staff_id,d_amount; if i_staff_id =2 then set @x1=@x1 d_amount; else set @x2=@x2 d_amount; end if; until 0 end repeat;whiledelimiter $$create procedure loop_demo()begin set @x=1,@x1=1; repeat set @x=@x 1; until @x>0 end repeat; while @x<1 do set @x=@x 1; end while; end; $$//创建事件调度器CREATE EVEN test_event_1 ON SCHEDULEEVERY 5 SECONDDOINSERT INTO dept(deptno,deptname)VALUES;//查看本地调度器状态 show variables like '%scheduler%'; //打开调度器 set global event_scheduler=1; //查看后台进程 show processlist; //创建一个新的定时器 定时清空表,防止表变大,这类触发器非常适合去定期清空临时表或者日志表 create event trunc_test on schedule every 1 minute do truncate table test; 禁用调度器或者删除 alter event test_event_1 disable; drop event test_event_1;
    
    事件调度器 说明
    优势 MySQL事件调度器部署在数据库内部由DBA或专人统一维护和管理,避免将一些数据库相关的定时任务部署到操作系统层,减少操作系统管理员产生误操作的风险,对后续的管理和维护也非常有益。例如,后续进行数据库迁移时无需再迁移操作系统层的定时任务,数据库迁移本身已经包含了调度事件的迁移
    使用场景 事件调度器适用于定期收集统计信息,定期清理历史数据,定期数据库检查(例如,自动监控和回复slave失败进程)
    注意事项 在繁忙且要求性能的数据库服务器上要慎重部署和启用调度去;过于复杂的处理更适合程序实现;开启和关闭事件调度器需要具有超级用户权限

    查看文章:

    • MySQL支持对MyISAM和MEMORY存储引擎的表进行表级锁定,对InnoDB存储引擎的表进行行集锁定。默认情况下是自动获得。
    • LOCK TABLES 可以用于锁定当前线程获得的表,如果表被其他线程锁定,当前线程一直等待到可以获取现有锁定为止。
    • UNLOCK TABLES 可以释放当前线程获得的任何锁定,当前线程执行另一个LOCK TABLES时,或当与服务器的连接被关闭时,所有由当前线程锁定的表被隐式地解锁。
    session_1 session_2
    获取表film_text 的read锁定 lock table fim_text read
    当前seesion可以查询记录 select * from fim_text 其他seesion也可以查询select * from fim_text
    其他session更新锁定表会等待锁 update fim_text .... 处于等待状态
    释放锁 unlock tables 等待
    sesion获取锁,更新成功

    mysql 通过set autocommit ,start transaction ,commit ,rollback等语句支持本地事务。默认情况下,mysql是自动提交(autocommit)的,如果需要明确的commit和rollback来提交和回滚事务,那么就需要通过明确的事务控制命令来开始事务,这是和Oracle的事务管理明显不同的地方。

    1. start transaction 或 begin语句可以开始一项新的事务
    2. commit 和rollback用来提交或者回滚事务。
    3. chain和release字句分别用来定义在事务提交或回滚之后的操作,chain会立即启动一个新事务,并且和刚才的事务具有相同的隔离级别,release则会断开和客户端的连接。
    4. set autocommit可以修改当前连接的提交方式,如果设置了set autocommit=0,则设置之后的所有事务读需要通过明确的命令进行提交或者回滚。

    如果只是对某些语句需要进行事务控制,则使用start transaction语句开始一个事务比较方便,这样事务结束之后可以自动回到自动提交的方式,如果希望所有的事务都不是自动提交的,那么通过修改autocommit来控制事务比较方便。

    start transation和commit and chain

    session_1 session_2
    从表actor查询 select * from actor 没有数据 从表actor查询 select * from actor 没有数据
    启动一个事务start transaction; insert into actor...
    查询actor select * from actor 仍然为空
    commit
    再次查询 就有 了 select * from actor...
    session_1 session_2
    自动提交 inset into actor...
    可以从表中查询到刚插入 select *from actor
    重新用star transaction 启动一个事务 start transaction; insert into actor...;用commit and chain命令提交 commit and chain;此时启动一个新的事务, insert into...
    刚插入的数据查不到 select * from actor...
    用commit 提交 commit;
    刚插入的可以查询到

    如果在锁表的期间,用start transaction 命令开始一个新的事务,会造成一个unlock tables被执行

    session_1 session_2
    查询一个actor_id=201,结果为空 select * from actor where actor_id=201; 从表里查询 同理结果为空
    对表加写锁 lock table actor write
    对表actor 的读操作被阻塞 select * from actor where actor_id=201
    插入数据 insert into actor(actor_id,..)values 等待
    回滚记录 rollback 等待
    用start transaction命令重新启动一个事务 等待
    开始一个事务,表锁被释放,可以查询;select ...where actor_id=201
    查到数据

    因此,在同一个事务中,最好不使用不同的存储引擎,否则rollback时需要对非事务类型的表进行特别的处理。因为,commit ,rollback 只能对事务类型的表进行提交和回滚。通常情况下,只对提交的事务记录到二进制的日志中,但是如果一个事务中包含非事务类型的表,那么回滚操作也会被记录到二进制日志中,以确保非事务类型表的更新可以被复制到从数据库中。在事务中可以通过定义savepoint,指定回滚事务部分,但是不能指定提交事务的一个部分。对于复杂的应用,可以定义多个不同的savepoint,满足不同的条件时,回滚不同的savepoint,需要注意的是,如果定义了相同名字的savepoint,则后面定义的savepoint会覆盖之前的定义。对于不需要使用的savepoint,可以通过release savepoint命令删除。事务回滚

    session_1 session_2
    从表中查询first_name='Simon'的记录为空 select * from....where first_name='simon' 从表中查询first_name='Simon'的记录为空 select * from....where first_name='simon'
    启动一个事务插入 一条数据 start transaction; inset ....values('simon'...)
    查询到刚插入的数据 select * from...where first_name='simon' 无法从actor查到session1刚插入的记录 select * from ... where first_name='simon'
    有数据 无数据
    定义一个 savepoint,名称为test savepoint test; insert into ...values
    查询到两条数据select *... 仍然查询不到数据 select * ...
    回滚到刚才定义的savepoint rollback to savepoint test
    从表actor查询到一条数据 第二天被回滚 select * from .... 仍然查询不到数据
    提交commit
    查询的到 查询的到

    MySql从5.0.3开始支持分布式事务,当前分布式事务只支持InnoDb存储引擎。一个分布式事务会涉及多个行动,这些行动本身是事务性。所有行动都必须一起成功完成,或者一起被回滚

    在mysql中,使用分布式事务的应用程序涉及一个或多个资源管理器和一个事务管理器。

    1. 资源管理器用于提供通向事务资源的途径。数据库服务器是一种资源管理器,该管理器必须可以提交或回滚由rm管理的事务。如:多台mysql数据库作为多台资源管理器或者几台mysql服务器和几台oracle服务器作为资源管理器。
    2. 事务管理器用于协调作为一个分布式事务一部分的事务。tm与管理每个事务的rm s进行通信。在分布式事务中,各个单个事务均是分布式事务的“分支事务”。分布式事务和各个分支通过一种命名方法进行标示。

    执行分布式的过程分为两阶段提交,发生时间有分布式事务的各个分支需要进行的行动已经被执行之后

    1. 在第一阶段,所有分支呗预备好,即它们被TM告知要准备提交。通常,这意味着用于管理分支的每个RM会记录对于被稳定保存的分支的行动。分支指示是否它们可以这么做,这些结果被用于第二阶段
    2. 在第二阶段,TM告知Rms是否要提交或回滚,如果在预备分支时,所有的分支指示它们将能够提交,则所有的分支被告知要提交。如果在预备时,有任何分支指示它将不能提交,则所有分支呗告知回滚。

    语法

    xa start xid 用于启动一个带给定xid值的xa事务。每个xa事务必须有一个唯一的xid值,因此该值当前不能被其他xa事务使用xa grtid[,beual[,formatId]] grtid 是一个分布式事务比较符,相同的分布式事务应该使用相同的gtrid,这样可以明确知道XA事务属于哪个分布式事务bequal 是一个分支限定符,默认值是空值。对于一个分布式事务中的每个分支事务,bqual指是唯一的formatId是一个数值,要用来标志 由gtrid和bqual值使用的格式,默认是1xa end xid[suspend [for migrate]]xa prepare xid使事务进入prepare 状态,也就是两阶段提交的第一个提交阶段xa commit xid[one phase]xa rollback xid用来提交和回滚具体的分支事务xa recover 返回当前数据库中处于PREPARE状态的分支事务的具体信息分布式的关键在于如何确保分布式事务的完整性,以及在某个分支出现问题时的故障解决,xa的相关命令就是提供给应用如何在多个独立的数据库之间进行分布式事务的管理,包括启动一个分支事务、使事务进入准备阶段以及事务的实际提交回滚操作等,
    

    例子

    session_1 in DB1 session_2 in DB2
    在数据库DB1 启动一个分布式的一个分支事务,xid 的gtrid 为 "test",bqual为"db1": xa start 'test','db1';分支事务插入一个数据 insert into actorvalues 对分支事务1进行第一阶段提交,进入prepare状态 :xa end 'test','db1'; xa prepare 'test','db1' 在数据库DB2 启动分布式事务 "test"的另外一个分支事务,xid的gtrid为"test".bqual为"db2"; xa start 'test','db2': 分支事务2在表film_actor 更新数据 最后 xa end 'test','db2' xa prepare 'test','db2'
    xa recover 查看当前分支事务状态 xa recover 查看当前分支事务状态
    两个事务进入准备提交状态,如果之前遇到任何错误,都应该回滚到所有分支,以确保事务的正确
    xa commit 'test','db1' xa commit 'test','db2'

    如果分支事务在执行到prepare状态是,数据库异常,且不能再支持启动,需要备份和binlog来回复数据,

    在MySql中,SQLMode常用来解决下面几类问题

    1. 通过设置SQL Mode,可以完成不同严格程度的数据校验,有效的保障数据准确性。
    2. 通过设置SQL Mode,为ANSI模式,来保证大多数SQL符合标准的Sql语法,这样应用在不同数据库之间进行迁移时,则不需要对业务SQL进行较大的修改
    3. 在不同数据库之间进行数据迁移之前,通过设置SQL Mode可以使MySQL上的数据更方便地迁移到目标数据库中
    查看 SQL Mode命令select @@sql_mode插入一个出国实际定义值的大小varcharinsert into value('123400000000000000000000000000000');//查看warning内容show warningsselect * from t 这里对插入的数据进行截取前10位设置SQL Mode为 严格模式set session sql_mode='STRICT_TRANS_TABLES'再次插入insert into value('123400000000000000000000000000000'); 直接给出ERROR,而不是warningSQL Mode常见功能校验日期是合法性set seesion sql_mode='ANSI'insert into t values('2007-04-31')结果是 插入值变成'0000-00-00 00:00:00' 并且系统给出warning 而在TRADITIONAL模式下,直接提示日期非法,拒绝插入,同时Mode也会报错qidon NO_BACKSLASH_ESCAPES模式,使反斜杠成为普通字符,在导入数据时,如果数据含有反斜杠字符,你们启动NO_BACKSLASH_ESCAPES模式,保证数据的正确性启动PIPES_AS_CONCAT。将||视为字符串连接符,在Oracle等数据库中,||被视为字符串的连接操作符,所以在其他数据库中含有||操作符的sql在MySql将无法执行,为了解决这个问题mysql提供了PIPES_AS_CONCAT模式、
    

    MySql从5.1版本开始支持分区,分区是指按照一定的规则,数据库把一个表分解成多个更小的,更容易管理的部分。就访问数据库的应用而言,逻辑上只有一个表或一个索引,但是实际上这个表可能由数10个物理分区对象组成,每个分区都是一个独立的对象,可以独自处理,可以作为表的一部分进行处理。分区对应用而言是完全透明的,不影响应用的业务逻辑

    优点

    • 和单个磁盘或者文件系统分区相比,可以存储更多数据
    • 优化查询。在where子句中包含分区条件,可以只扫描必要的一个或多个分区来提高查询效率;同时在涉及SUM这类聚合函数的查询时,可以容易的在每个分区上并行处理,最终只需要汇总所有分区的结果
    • 对于已经过期或者不需要保存的数据,可以通过删除与这些数据有关的分区来快速删除数据
    • 跨多个磁盘来分散数据查询,以获得更大的查询吞吐量

    分区有利于管理非常大的表,它采用 分而治之的逻辑,分区引入分区键的概念,分区键用于根据某个区间键,特定值列表或者HASH函数执行数据的聚集,让数据根据规则分布在不同的分区中,让一个大对象变成一些小对象

    show VARIABLES like '%partition%' 查看是否支持分区Mysql支持大部分存储引起如MyISAM,INNODb,Memory等存储引擎,创建分区,在5.1版本中,同一个分区表的所以分区必须使用同一个存储引擎;在同要给表上,不能对一个分区使用MyISAM引擎和Innodb引擎,但是在同一个MySQL服务器服务器上,甚至同一个数据库中,对于不同的分区表使用不同的存储引擎
    

    分区类型

    • range分区:基于一个给定连续区间范围,把数据分配到不同的分区。
    • LIST分区:类似RANGE分区,区别在LIST分区是基于枚举出的值列表分区,RANGE是基于给定的连续区间范围分区
    • HASH分区:基于给定的分区个数,把数据分配到不同的分区
    • KEY分区:类似HASH分区

    在5.1版本中,RANGE分区,LIST分区,HASH分区要求分区键都是int类型,key分区,可以使用其他类型(除了BLOB和TEXT类除外)作为分区键分区表的主键/唯一键必须包含分区键,不能使用主键/唯一键,要么分区表的主键/唯一键都必须包含分区键,分区的名字是不区分大小写的

    range分区CREATE TABLE emp( id int not null, ename varchar, hired date not null DEFAULT '1970-01-01', separated date NOT null DEFAULT '9999-12-21', job varchar not null, store_id int not null)partition by range( PARTITION p0 VALUES less than , PARTITION p1 VALUES less than , PARTITION p2 VALUES less than ;//上述的分区方案将storid,1-9分到p0区,10-19分到p1区,等如果插入大于30,会出现错误,因为没有规则保护大于30的INSERT into emp VALUES('2322','milk','1993-12-23','1993-12-23','click',19);//可以//Table has no partition for value 40INSERT into emp VALUES('2322','milk','1993-12-23','1993-12-23','click',40);添加分区alter table emp add partition(partition p3 values less than maxvalue);maxvalue表示最大的可能的整数值mysql 支持在values less than 语句中加入表达式比如以日期作为分区CREATE TABLE emp( id int not null, ename varchar, hired date not null DEFAULT '1970-01-01', separated date NOT null DEFAULT '9999-12-21', job varchar not null, store_id int not null)partition by range(year(separated ))( PARTITION p0 VALUES less than , PARTITION p1 VALUES less than , PARTITION p2 VALUES less than ;MySQl 5.5改进了range分区给你,通过支持非整数分区,创建日期分区就不需要通过函数进行转换partition by range(separated )( PARTITION p0 VALUES less than ('1996-01-01'), PARTITION p1 VALUES less than ('2001-01-01'), PARTITION p2 VALUES less than ('2006-01-01'));
    

    rang分区的功能适用一下情况

    • 当需要删除过期的数据,只需要简单的alter table emp drop partition p0 来删除p0 分区中的数据。对于具有上百万条记录的表来说,删除分区要比运行一个delete语句有效的多
    • 经常运行包含分区键的查询,MyySql可以很快地确定只有某一个或者某些分区需要扫描,因为其他分区不可能包含有符合该where字句的任何记录。例如检索id大于25的记录数,MySql只需要扫描p2分区即可
    explain partition select count from emp where store_id>=25
    

    List分区List分区是建立离散的值列表告诉数据库特定的值属于哪个分区,LIST分区在很多方面类似于RANGE分区,区别在于LIST分区是从属于一个枚举列表,RANGE分区是从属于一个连续区间值的集合

    create table expenses( expense_date date not null, category int, amount decimalpartition by list ( partition p0 values in, partition p1 values in, partition p2 values in, partition p3 values in, partition p4 values inLIST分区不存在类似于VALUES LESS THAN MAXVALUE这样的值 在MYSQL5.5支持非整数分区
    

    Columns分区Column分区是5.5引入的分区类型,引入Columns分区解决了MySQL5.5版本之前RANGE和LIST分区值值支持整数分区,从而导致需要额外的函数计算得到整数值或者通过额外的转换表来转换为整数在分区的问题Column分区可以细分为RANGE Columns分区和LIST Columns分区,RANGE Columns分区和LIST Columns分区都支持整数,日期时间,字符串三大数据类型对于Range分区和List分区,Colums分区的亮点除了支持数据类型增加之外,还支持多列分区

    create table rc3(a int,b int)parition by range columns( parition p01 values less than, parition p01 values less than, parition p01 values less than, parition p01 values less than(maxvalue,maxvalue))
    

    Hash分区hash分区主要是分数热点读,确保数据在预先确定个数的分区中尽可能平均分布。对一个表执行HASH分区时,Mysql会对分区间应用一个散列函数,以确定数在n个分区中的那个分区中。mysql支持两种hash分区,常规的hash分区和线性hash分区,常规的hash使用取模算法,线性hash分区使用的一个线性的2的幂的运算法则

    create table emp(id int not null.ename varchar,hired date not null default '1907-01-01',sparated date null null default '8888-12-31',job varchar not null,store_id int not null) partition by hashpartitions 4;这里创建了一个常规的hash 使用 partition by hash其中expr是某列值或一个整数值的表达式返回值。 partition num 对分区类型,分区键,分区个数进行定义,上述基于store_id列hash分区,表被分为4个分区我们可以计算出它被保存在哪个分区中假设,假设记录的分区编号为N,那么N=MOD,例如emp表中有4个分区,插入一个store_id为234的 mod=2,倍保存在第二个分区
    

    表达式‘expr’可以是MySQL中有效的任何函数或者是其他表达式,只要它们返回一个既非常数也非随机数的整数。每当插入更新删除一行数据,这个表达式就需要计算一次,意味着非常复杂的表达式可能会引起性能问题常规的HASH分区通过去模的方式去讲数据平均分布在每个分区上,让每个分区管理的数据都减少,提高了查询的效率;可是当我们需要增加分区或者合并分区的时候,问题就出现了,假设原来是5个常规hash分区,现在需要新增一个常规hash分区,原来的去模算法是mod根据余数0-4分布在5个分区上,现在新增一个分区,取模算法变为mod根据余数0-5分区在6个分区中,原来5个分区的数据大部分都需要通过重新计算重新分区,常规的hash在分区管理上带来的代价太大了。不适合灵活变动分区的需求,Mysql提供了线性hash分区

    create table emp(id int not null.ename varchar,hired date not null default '1907-01-01',sparated date null null default '8888-12-31',job varchar not null,store_id int not null) partition by linear hashpartitions 4;计算编号为n的分区首先找到下一个大于等于num的2的幂,这个值设为v,v的计算公司v=power(2,ceiling(log =power(2,ceiling) =power(2,ceiling =power =4其次设置n=f(column_list)&,现在计算store_id=234对应的n值n=f(column_list)& =234& =2当n》=num设置n=n&对于store_id=234由于n=2《4,所以直接判断这个会被存放到第二分区
    

    线性hash分区的优点在于,在分区维护上(包含增加,删除,合并,拆分分区)时,Mysql能够处理得更加迅速;缺点是对比常规hash分区的时候,线性hash分布不太均衡

    key分区按照key分区进行分区非常类似于按照hash进行分区,只不过hash分区允许使用用户自定义的表达式,而KEY分区不行使用用户自定义的表达式,需要使用MySQl服务器提供的hash函数;同时hash分区只支持整数分区,而key分区支持除了blobor text类型外其他类型的列作为分区键

    create table emp(id int not null.ename varchar,hired date not null default '1907-01-01',sparated date null null default '8888-12-31',job varchar not null,store_id int not null) partition by key partitions 4;如果不知道分区键,默认为主键,没有主键会选择非空唯一键作为分区键
    

    子分区子分区是分区表对每个分区的再次分割。又被称为复合分区,mysql5.1开始支持对已经通过range或者list分区了的表在进行子分区

    create table ts(id int,purchased date) partition by range(year(purchased)) subpartition by hash(to_days(purchased))subpartitions 2(partition p0 values less than ,partition p0 values less than ,partition p0 values less than )
    

    在分区中的null值在mysql不禁止分区键上使用null,分区键可能是一个字段或者一个用户定义的表达式,一般情况下,mysql的分区把null当做零值,或者一个最小值处理

    分区管理

    删除分区alter table emp_date drop partition p2;增加分区alter table emp_date add partition(partition p5 value less than拆分p3分区,分为p2和p3分区分区alter table emp_date reorganize partition p3 into(partition p2 values less than,parition p3 values less than ;合并分区alter table emp_date reogranize partition p1,p2,p3 into(partition p1 values less than 重新定义list分区时,只能重新定义相邻的分区,不能跳过list分区进行重新定义
    

    hash&key管理

    不能以range和list分区表删除分区的方式,而是跳过alter table coalesce partition 来合并或分区以原先4个分区为例alter table emp coalesce partition 2 //减少分区到2个alter table emp coalesc partition 8 //不能增加分区要增加分区alter table emp add partition partitions 8;
    

    参考文章

    • 深入浅出Mysql,数据库开发、优化和管理维护

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net:mysql中的表锁定及事务控制,事务控

    关键词: