首页 > 数据库 >SQL-数据库中ACID和事务性以及不同数据库的比较

SQL-数据库中ACID和事务性以及不同数据库的比较

时间:2024-09-26 17:02:21浏览次数:10  
标签:语句 事务 -- INTO SQL ACID 数据 数据库

基本理念

 
静态数据集是世界在某一特定瞬间的快照
Static datasets are split-second snapshots of whatever the world looked like at one moment 

世界就会继续前进,数据集需要跟上步伐以保持其有用性
the world moves on, and the dataset needs to catch up to remain useful

变化和宇宙一样古老。然而,不知何故,在处理数据时,我们往往将变化视为次要的事情
  change is as old as the universe. 
  Yet somehow, when dealing with data, we often consider change as merely an afterthought.
  
因此,作为数据管理系统,管理变化不是可选项。然而,正确管理变化是困难的。
  As a data management system, managing change is thus not optional. 
		However, managing changes properly is difficult. 
ACID is an acronym that stands for Atomicity, Consistency, Isolation and Durability. 
   ACID是一个缩写,代表原子性、一致性、隔离性和持久性

DQL (Data Query Language)是用来查询数据库中的数据的语言,如 SELECT 语句
   Definition Munipulation  Query Control 

   TPL语句包括BEGIN TRANSACTION、COMMIT和ROLLBACK

技术经验和概念

数据库事务性是指数据库事务的一种特性,它保证了在一个事务中的所有操作要么全部成功,要么全部失败。数据库事务性的主要特点包括:一致性、原子性、隔离性和持久性
   A原子性是指事务是一个不可分割的工作单元,事务中的所有操作要么全部完成,要么全部不完成

   C数据库的状态必须满足所有的一致性约束
  
   I隔离性是指并发执行的事务之间不会互相影响,每个事务都有自己独立的工作空间,事务之间的操作互不干扰
       隔离这些事务的一个简单方法是依次执行它们
       然而,这样做会非常慢。数千个请求可能不得不等待一个特别慢的请求。为了避免这个问题,事务通常是交错执行的

   D持久性是指一旦事务提交,那么这个事务中的所有操作都将永久地保存到数据库中,即使在此后发生系统故障,这些操作的结果也不会丢失

通常数据库的事务涉及到的语句有:
一组 DML(Data Munipulation Language,数据操作语言)语句,这组DML语句修改后数据将保持较好的一致性; 操作表的语句,如插入、修改、删除等;
一个 DDL(Data Definition Language,数据定义语言)语句,操作数据对象的语言,有create、alter、drop。
一个 DCL(Data Control Language,数据控制语言)语句,主要有grant、revoke语句

具体实现

原子性实现:
    原子性通常通过日志和恢复机制实现
一致性约束
    通过数据库的约束机制(如外键、唯一性约束)和触发器等实现。事务在提交时,系统会检查所有的约束条件,确保数据的一致性。

隔离性--事务和事务之间
   MySQL数据库为我们提供的四种隔离级别
      Read uncommitted (读未提交)  Read committed (读已提交)
      Repeatable read (可重复读)   Serializable (串行化)
          Serializable这样的级别,就是以锁表的方式使得其他的线程只能在锁外等待
    Oracle数据库中,只支持Serializable (串行化)级别
          和Read committed (读已提交)这两种级别,其中默认的为Read committed级别
    通过锁机制和多版本并发控制(MVCC)等技术实现,避免 “脏读”、“不可重复读”和“幻读”等问题。
持久性是通过数据库备份和恢复系统来实现的
    服务端有日志记录功能,新操作是记录到日志中的,commit会将日志中的记录刷到硬盘中,rollback就是将日志中的记录删除掉了

Transaction Isolation Levels 事务隔离级别
	
显示提交和隐式提交   
    编程式事务管理中,开发者显式地控制事务的开始、提交和回滚。
    隐式提交==自动提交模式(auto-commit mode)

  开启事务:start transaction 
  执行SQL语句
  提交事务:commit; 回滚事务:rollback	

   SQL中的开启事务 BEGIN TRANSACTION
   执行SQL操作
   commit;或者rollback; # 事务结束

操作有
   修改隔离级别  开启事务   执行SQL语句  commit或者rollback(回滚)

DuckDB

#############################################################
BEGIN TRANSACTION;
INSERT INTO customer VALUES (42, 'DuckDB Labs');
INSERT INTO orders VALUES (42, 'stale bread');
COMMIT;

BEGIN TRANSACTION;
INSERT INTO orders VALUES (42, 'iceberg lettuce');
INSERT INTO orders VALUES (42, 'dried worms');
ROLLBACK;
SELECT * FROM orders;
#####
“auto-commit” mode 
###########################################################
Consistency
  PRIMARY KEY or FOREIGN KEY 
constraint - 限制-约束
   主关键字(primary key)是表中的一个或多个字段,它的值用于唯一的标识表中的某一条记录。强制表的实体完整性
   ‌ Unique key‌ 也是用来确保数据的唯一性,但它允许有多个空值
   
    primary -初级的-主要的 primary school(小学)
            primary color(原色)是指红、蓝和绿
	foreign -外国的
    foreign key 就是表与表之间的某种约定的关系,由于这种关系的存在,能够让表与表之间的数据
	   级联执行 阻止执行
      
#############################################################
不同用户不同程序
  A database can have many clients interacting with it at the same time,
多个应用程序--duckdb没有这个,it is not a client/server database
duckdb在单个应用程序
单个应用程序-单个连接 full multi-client support within a single application
DuckDB 在单个应用程序内有完整的多客户端支持。
   用户可以创建多个客户端,它们都连接到同一个 DuckDB 实例。事务可以并发运行,并且它们使用快照隔离(Snapshot Isolation)来实现隔离

#############################################################
 transactional systems (OLTP)
 and analytical systems (OLAP).

解释说明

BEGIN TRANSACTION;
-- 从CSV文件加载数据集
LOAD DATA FROM 'data.csv' INTO TABLE raw_data;
-- 清理NULL值
DELETE FROM raw_data WHERE column IS NULL;
-- 删除不完整的行
DELETE FROM raw_data WHERE incomplete = true;
-- 如果一切顺利,则提交事务
COMMIT;
-- 如果发现错误,则回滚事务
-- ROLLBACK;

通过在事务中使用断言,可以在数据处理过程中设置检查点,确保在提交更改之前满足特定的条件。如果条件不满足,事务将不会提交,从而避免了可能的数据损坏。


宏与序列

宏
macro may only be a single SELECT statement (similar to a VIEW),
  but it has the benefit of accepting parameters.

CREATE MACRO add(a, b) AS a + b;
CREATE MACRO ifelse(a, b, c) AS CASE WHEN a THEN b ELSE c END;
CREATE MACRO plus_one(a) AS (WITH cte AS (SELECT 1 AS a) SELECT cte.a + a FROM cte);

CREATE MACRO assert(condition, message) AS
    CASE WHEN NOT condition THEN error(message) END;
SELECT assert(
           (SELECT count(*) FROM people) > 0,
           'People should not be empty'
       );
序列
CREATE SEQUENCE id_sequence START 1;
CREATE TABLE tbl (id INTEGER DEFAULT nextval('id_sequence'), s VARCHAR);
INSERT INTO tbl (s) VALUES ('hello'), ('world');
SELECT * FROM tbl;

Nextval是一个用于获取序列下一个值的函数,通常用于数据库中的自增主键。
  它可以确保每个新插入的行都有一个唯一的主键,保障了数据的正确性

不同sql之间的操作命令

###自增
    oracle 
         序列
    	 从Oracle 12c 开始,引入了 IDENTITY 列,可以用于自动递增的主键字段
    Mysql
     
        create table table_name (id int not null auto_increment, name varchar(255) not null)
    
    SQLServer
         自增长(auto increment)功能
    
    PostgreSQL
       没有像MySQL那样的AUTO_INCREMENT关键字,但通过序列和相关的函数,可以实现类似的功能
        在PostgreSQL(简称PG)中,实现字段自增的主要方式是通过使用序列(sequence)。
    	序列是一种特殊的数据库对象,用于生成一个唯一的数值,通常用于主键字段以实现自增功能。
    	
        CREATE SEQUENCE sequence_name [AS data_type] 
    	        [INCREMENT [BY] increment] 
    			[START WITH start_value] 
    			[MINVALUE min_value] [MAXVALUE max_value] [CACHE cache_value] [CYCLE];
    	ALTER TABLE students ALTER COLUMN id SET DEFAULT nextval('seq_a');

###更新时间等
    ###mysql 更新
        根据某一字段值查询数据库中是否有记录,
    	    有则更新,没有则插入。这个时候就可以用到ON DUPLICATE KEY UPDATE
         INSERT INTO users (id, email, name)
         VALUES (1, '[email protected]', 'John Doe')
         ON DUPLICATE KEY UPDATE name = VALUES(name);
    	      on duplicate key update 语句根据主键id或唯一键来判断当前插入是否已存在
    		  记录已存在时,只会更新on duplicate key update之后指定的字段
    	创建时间和更新时间
          create table orders(
          orderId int primary key auto_increment comment 'orderId',
          userId varchar(10) not null,
          create_time datetime DEFAULT current_timestamp comment'创建时间',
          update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’)
          ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
    	  
    ###Postgresql
         postgresql更新时间戳需要通过触发器来实现
        创建时间(create_at) 、更新时间(update_at)
        update_at 字段自动UPDATE就需借助数据库触发器即Trigger,也可将触发器理解成自定义的SQL函数。
         -- 创建触发器
         CREATE OR REPLACE FUNCTION update_modified_column()
         RETURNS TRIGGER AS $$
         BEGIN
         	#需要更新字段
             NEW.update_at = now();
             RETURN NEW;
         END;
         $$ language 'plpgsql'; 
          -- 设置触发器
         CREATE TRIGGER trigger_name BEFORE 
         UPDATE ON table_name 
         FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
         
         -- 删除触发器
         DROP TRIGGER update_table_name_update_at ON sms001_user_info ;
     
    ###Oracle
        MERGE语句用于将一个表中的数据合并到另一个表中。
    	  MERGE语句可以根据指定的条件判断目标表是否已存在某条记录,如果存在则更新记录,如果不存在则插入记录 
        MERGE INTO 语句可用于在目标表中执行插入、更新和删除操作,适用于各种数据同步、数据清洗和数据修复场景	  
          MERGE INTO:不存在则 insert, 存在则 update  
    ###PG 
        PostgreSQL upsert
          在关系型数据库中,upsert是一个组合词,即当往表中插入记录,如果该记录已存在则更新,否则插入新记录  
          insert on conflict do 功能,即UPSERT的效果	  
    ###SQLite
         该条数据不存在,插入,数据存在,进行更新操作
         使用INSERT OR REPLACE命令  
    ###Duckdb
           INSERT OR REPLACE INTO  
           INSERT INTO 会检测主键, 如果有已经存在的主键,则会报错inserts new rows into a table 
           INSERT OR IGNORE INTO	 如果不存在,则添加,如果存在,则不操作   
      
    ###Hive  
         hive表数据更新  insert overwrite/merge into 
         insert overwrite : 是把本次数据覆盖目标表里原先的数据(不管它原本有没有!),
    	   本质是先把目标表的数据清空 truncate,然后对其 insert into 写入数据	
        insert into 是对于目标表追加数据的,对的,是追加!!!
        insert overwrite 会对目标表数据进行覆盖,对的,是覆盖!!	   
    

参考

https://duckdb.org/2024/09/25/changing-data-with-confidence-and-acid.html

标签:语句,事务,--,INTO,SQL,ACID,数据,数据库
From: https://www.cnblogs.com/ytwang/p/18433742

相关文章

  • MySQL参数优化指南
    一、引言        MySQL作为一种广泛应用的开源关系型数据库,其性能直接影响应用的响应速度和用户体验。通过合理的参数配置,可以显著提升数据库的性能、稳定性和并发处理能力。本文将深入探讨MySQL的一些主要参数,包括它们的意义、优化方向和预期效果。二、主要参数介绍......
  • Token: 数据库、存储系统和API安全的应用
    一.TokenToken是一种常见的计算机术语,它在不同的上下文中有不同的含义。在身份验证和授权的上下文中,Token通常指的是服务端生成的一串字符串,作为客户端进行请求的一个令牌。当用户登录后,服务器会生成一个Token并返回给客户端,客户端在后续的请求中携带这个Token,以此来验证用户......
  • 将一个表中的数据循环插入另外一个表中的mysql的存储过程
    DELIMITER$$CREATEPROCEDUREInsertDataIntoSysDictData()BEGIN--声明变量DECLAREdoneINTDEFAULTFALSE;DECLAREattribute_nameVARCHAR(255);DECLAREapply_modeVARCHAR(255); DECLAREcounter1INTDEFAULT0;--游标声明DECLAREc......
  • MySQL variables:max_connections&&max_user_connections
    结论1:max_connections变量的意义是限制当前mysqlserver中允许同时连接的不同用户数,并不对相同用户的多次连接进行限制结论2:max_user_connections变量的意义是限制当前mysqlserver中允许同时连接的相同用户的连接数,不对连接的不同用户数进行限制结论3:对max_connections变量的......
  • PG数据库查询字段备注信息
    在PostgreSQL数据库中,要查询某个表的字段名(即列名)及其备注信息,可以使用information_schema.columns视图来获取列名,并结合pg_description和pg_class等系统表来获取列的备注信息。下面是一个示例SQL查询,假设我们要查询名为your_table的表的列名及备注:SELECT......
  • MySQL variables:binary-as-hex
    不注意到这个变化的话,还挺折腾人的。在MySQL8.0.19ReleaseNotes里,有这么一段话:Whenthemysqlclientoperatesininteractivemode,the--binary-as-hexoptionnowisenabledbydefault.Inaddition,outputfromthestatus(or\s)commandincludesthislinewhenth......
  • 数据库系统------ER图中的符号
    矩形实体集:实体是指在现实世界中可独立存在的对象或事物,实体集就是相同类型实体的集合,可以看成是一个类画在实体集中的属性A1表示简单属性A2表示复合属性,即由多个子属性组成的属性,这里是指A2.1和A2.2,例如,地址可以分为街道、城市和邮政编码A3表示多值属性,即该属......
  • MySQL 8.0 绿色安装
    下载访问MySQL的下载地址:https://dev.mysql.com/downloads/mysql/选择系统、版本、glic版本后,下载包缩包https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.39-linux-glibc2.17-x86_64.tar.xz注:如果不知道该选什么glic版本,可以运行命令:ldd--version输出:ldd(GNUl......
  • MySQL variables:thread_handling
    在使用MySQL数据库时,我们经常会遇到多个客户端同时访问数据库的情况。为了处理并发请求,MySQL提供了thread_handling参数,用于控制线程的管理方式thread_handling参数的作用thread_handling参数用于控制MySQL如何处理客户端的连接请求。它可以影响数据库的性能、吞吐量以......
  • 为什么要考工信部人才交流中心PostgreSQL认证?
    为什么要考工信部人才交流中心PostgreSQL认证?随着PostgreSQL数据库在国内的使用越来越多,想要了解PostgreSQL认证的人也多了起来,有了工信部人才交流中心的PostgreSQL证书,可以获得多方面的好处。工信部人才交流中心PostgreSQL认证的发起背景:-技术优势契合信创需求:PostgreSQL数......