事务处理 #

一、事务概述 #

1.1 什么是事务 #

事务是一组操作的逻辑单元，要么全部成功，要么全部失败：

text 
事务特点 (ACID)：
├── Atomicity (原子性) - 全部成功或全部失败
├── Consistency (一致性) - 数据保持一致状态
├── Isolation (隔离性) - 事务间相互隔离
└── Durability (持久性) - 提交后永久保存

1.2 OrientDB事务特点 #

text 
OrientDB事务特点：
├── 支持ACID事务
├── 支持多文档事务
├── 支持乐观锁
├── 支持嵌套事务
└── 自动提交模式

二、基本事务操作 #

2.1 开始事务 #

sql 
BEGIN

2.2 提交事务 #

sql 
COMMIT

2.3 回滚事务 #

sql 
ROLLBACK

2.4 完整事务示例 #

sql 
BEGIN
INSERT INTO Person SET name = 'Tom', age = 30
INSERT INTO Person SET name = 'Jerry', age = 25
COMMIT

2.5 事务回滚示例 #

sql 
BEGIN
INSERT INTO Person SET name = 'Tom', age = 30
ROLLBACK

三、事务隔离级别 #

3.1 隔离级别类型 #

级别 说明 问题
READ UNCOMMITTED 读未提交 脏读
READ COMMITTED 读已提交 不可重复读
REPEATABLE READ 可重复读 幻读
SERIALIZABLE 可串行化 无

3.2 OrientDB默认隔离级别 #

OrientDB默认使用 READ COMMITTED 隔离级别。

3.3 设置隔离级别 #

sql 
ALTER DATABASE SET tx isolationLevel = READ_COMMITTED
ALTER DATABASE SET tx isolationLevel = REPEATABLE_READ

四、乐观锁 #

4.1 版本控制 #

每条记录都有版本号：

sql 
SELECT @rid, @version, name FROM Person WHERE name = 'Tom'

4.2 乐观锁更新 #

sql 
UPDATE Person SET name = 'Tom Hanks' WHERE @rid = #12:0 AND @version = 5

4.3 版本冲突检测 #

sql 
LET $record = SELECT @rid, @version FROM Person WHERE name = 'Tom'
UPDATE Person SET name = 'Tom Hanks' 
WHERE @rid = $record[0].@rid AND @version = $record[0].@version

4.4 处理版本冲突 #

sql 
TRY {
    UPDATE Person SET name = 'Tom Hanks' WHERE @rid = #12:0 AND @version = 5
} CATCH {
    SELECT 'Version conflict, please retry' AS error
}

五、事务示例 #

5.1 银行转账 #

sql 
BEGIN
LET $fromAccount = SELECT FROM Account WHERE id = 'A001'
LET $toAccount = SELECT FROM Account WHERE id = 'A002'

IF ($fromAccount[0].balance >= 100) {
    UPDATE Account SET balance = balance - 100 WHERE id = 'A001'
    UPDATE Account SET balance = balance + 100 WHERE id = 'A002'
    INSERT INTO Transaction SET 
        fromAccount = $fromAccount[0].@rid,
        toAccount = $toAccount[0].@rid,
        amount = 100,
        createdAt = sysdate()
    COMMIT
} ELSE {
    ROLLBACK
    SELECT 'Insufficient balance' AS error
}

5.2 订单创建 #

sql 
BEGIN
LET $order = INSERT INTO Order SET 
    customerId = 'C001',
    status = 'pending',
    createdAt = sysdate()
RETURN @rid

LET $items = [
    {'productId': 'P001', 'quantity': 2, 'price': 100},
    {'productId': 'P002', 'quantity': 1, 'price': 200}
]

FOREACH ($item IN $items) {
    INSERT INTO OrderItem SET 
        orderId = $order[0].@rid,
        productId = $item.productId,
        quantity = $item.quantity,
        price = $item.price
}

UPDATE Order SET totalAmount = (
    SELECT SUM(quantity * price) FROM OrderItem WHERE orderId = $order[0].@rid
) WHERE @rid = $order[0].@rid

COMMIT
RETURN $order

5.3 批量导入 #

sql 
BEGIN
LET $data = SELECT FROM CSV '/data/import.csv'
FOREACH ($row IN $data) {
    INSERT INTO Person SET 
        name = $row.name,
        age = $row.age,
        email = $row.email
}
COMMIT

六、嵌套事务 #

6.1 嵌套事务概念 #

OrientDB支持嵌套事务：

sql 
BEGIN
INSERT INTO Person SET name = 'Tom'

BEGIN
INSERT INTO Person SET name = 'Jerry'
COMMIT

COMMIT

6.2 嵌套事务回滚 #

sql 
BEGIN
INSERT INTO Person SET name = 'Tom'

BEGIN
INSERT INTO Person SET name = 'Jerry'
ROLLBACK

COMMIT

七、事务超时 #

7.1 设置超时 #

sql 
ALTER DATABASE SET tx.timeout = 30000

7.2 超时处理 #

sql 
BEGIN
SET TRANSACTION TIMEOUT 5000
INSERT INTO Person SET name = 'Tom'
COMMIT

八、并发控制 #

8.1 锁类型 #

锁类型 说明
共享锁 允许读，阻止写
排他锁 阻止读写

8.2 锁定记录 #

sql 
SELECT FROM Person WHERE @rid = #12:0 LOCK RECORD

8.3 锁定超时 #

sql 
SELECT FROM Person WHERE @rid = #12:0 LOCK RECORD TIMEOUT 5000

8.4 避免死锁 #

text 
死锁避免策略：
├── 按固定顺序访问资源
├── 设置合理的超时时间
├── 减少事务持有时间
└── 使用乐观锁替代悲观锁

九、事务日志 #

9.1 查看事务状态 #

sql 
SELECT FROM metadata:transactions

9.2 事务统计 #

sql 
SELECT 
    COUNT(*) AS activeTransactions
FROM metadata:transactions

十、分布式事务 #

10.1 分布式事务特点 #

text 
分布式事务特点：
├── 跨多个节点
├── 两阶段提交
├── 最终一致性
└── 需要额外配置

10.2 分布式事务配置 #

xml 
<handler class="com.orientechnologies.orient.server.hazelcast.OHazelcastPlugin">
    <parameter name="enabled" value="true"/>
</handler>

十一、事务最佳实践 #

11.1 事务设计原则 #

text 
事务设计原则：
├── 保持事务简短
├── 避免长时间事务
├── 合理设置隔离级别
├── 正确处理异常
└── 避免热点数据竞争

11.2 错误处理 #

sql 
TRY {
    BEGIN
    INSERT INTO Person SET name = 'Tom'
    COMMIT
} CATCH {
    ROLLBACK
    SELECT 'Transaction failed: ' + $error AS message
}

11.3 重试机制 #

sql 
LET $maxRetries = 3
LET $retry = 0
LET $success = false

WHILE ($retry < $maxRetries AND $success = false) {
    TRY {
        BEGIN
        UPDATE Account SET balance = balance - 100 WHERE id = 'A001'
        UPDATE Account SET balance = balance + 100 WHERE id = 'A002'
        COMMIT
        LET $success = true
    } CATCH {
        ROLLBACK
        LET $retry = $retry + 1
    }
}

IF ($success = false) {
    SELECT 'Transaction failed after ' + $maxRetries + ' retries' AS error
}

十二、实际应用示例 #

12.1 库存扣减 #

sql 
BEGIN
LET $product = SELECT FROM Product WHERE id = 'P001' LOCK RECORD

IF ($product[0].stock >= 10) {
    UPDATE Product SET stock = stock - 10 WHERE id = 'P001'
    INSERT INTO InventoryLog SET 
        productId = $product[0].@rid,
        quantity = -10,
        type = 'out',
        createdAt = sysdate()
    COMMIT
} ELSE {
    ROLLBACK
    SELECT 'Insufficient stock' AS error
}

12.2 用户注册 #

sql 
BEGIN
LET $existing = SELECT FROM User WHERE email = 'tom@example.com'

IF ($existing.size() = 0) {
    INSERT INTO User SET 
        email = 'tom@example.com',
        username = 'tom',
        password = hash('SHA-256', 'password'),
        status = 'pending',
        createdAt = sysdate()
    INSERT INTO UserProfile SET 
        userId = @rid,
        avatar = 'default.png'
    COMMIT
} ELSE {
    ROLLBACK
    SELECT 'Email already exists' AS error
}

12.3 数据迁移 #

sql 
BEGIN
LET $batch = SELECT FROM OldTable LIMIT 1000
FOREACH ($record IN $batch) {
    INSERT INTO NewTable SET 
        field1 = $record.oldField1,
        field2 = $record.oldField2,
        migratedAt = sysdate()
    DELETE FROM OldTable WHERE @rid = $record.@rid
}
COMMIT

十三、总结 #

事务处理要点：

操作 语法 说明
开始 BEGIN 开始事务
提交 COMMIT 提交事务
回滚 ROLLBACK 回滚事务
锁定 LOCK RECORD 锁定记录
异常处理 TRY/CATCH 错误处理

下一步，让我们学习存储过程！

最后更新：2026-03-27