存储过程、触发器和用户定义的函数Stored procedures, triggers, and user-defined functions

Azure Cosmos DB 提供 JavaScript 的语言集成式事务执行。Azure Cosmos DB provides language-integrated, transactional execution of JavaScript. 在 Azure Cosmos DB 中使用 SQL API 时,可以采用 JavaScript 语言编写存储过程触发器用户定义的函数 (UDF)When using the SQL API in Azure Cosmos DB, you can write stored procedures, triggers, and user-defined functions (UDFs) in the JavaScript language. 可以使用 JavaScript 编写可在数据库引擎内部执行的逻辑。You can write your logic in JavaScript that executed inside the database engine. 可以使用 Azure 门户Azure Cosmos DB 中的 JavaScript 语言集成式查询 APICosmos DB SQL API 客户端 SDK 来创建及执行触发器、存储过程与 UDF。You can create and execute triggers, stored procedures, and UDFs by using Azure portal, the JavaScript language integrated query API in Azure Cosmos DB or the Cosmos DB SQL API client SDKs.

使用服务器端编程的优势Benefits of using server-side programming

使用 JavaScript 编写存储过程、触发器和用户定义的函数 (UDF) 可以生成具有以下优势的丰富应用程序:Writing stored procedures, triggers, and user-defined functions (UDFs) in JavaScript allows you to build rich applications and they have the following advantages:

  • 过程逻辑: 作为一种高级编程语言,JavaScript 提供用户熟悉的丰富接口来表达业务逻辑。Procedural logic: JavaScript as a high-level programming language that provides rich and familiar interface to express business logic. 可以针对数据执行一系列复杂操作。You can perform a sequence of complex operations on the data.

  • 原子事务: Azure Cosmos DB 保证在单个存储过程或触发器内执行的数据库操作具有原子性。Atomic transactions: Azure Cosmos DB guarantees that the database operations that are performed within a single stored procedure or a trigger are atomic. 此原子功能可让应用程序在单个批中合并相关操作,因此操作要么全部成功,要么全部失败。This atomic functionality lets an application combine related operations into a single batch, so that either all of the operations succeed or none of them succeed.

  • 性能: JSON 数据在本质上会映射到 JavaScript 语言类型系统。Performance: The JSON data is intrinsically mapped to the JavaScript language type system. 这种映射可以实现多种优化,例如,在缓冲池中将 JSON 文档惰性具体化,并使其可按需供执行代码使用。This mapping allows for a number of optimizations like lazy materialization of JSON documents in the buffer pool and making them available on-demand to the executing code. 还有其他与传送业务逻辑到数据库相关的性能优势,包括:There are other performance benefits associated with shipping business logic to the database, which includes:

    • 批处理: 可将插入等操作分组到一起并批量提交。Batching: You can group operations like inserts and submit them in bulk. 用于创建单独事务的网络流量延迟成本和存储开销显著降低。The network traffic latency costs and the store overhead to create separate transactions are reduced significantly.

    • 预编译: 存储过程、触发器和 UDF 是隐式预编译成字节代码格式,这是为了避免每次脚本调用时产生编译成本。Pre-compilation: Stored procedures, triggers, and UDFs are implicitly pre-compiled to the byte code format in order to avoid compilation cost at the time of each script invocation. 预编译使得存储过程的调用速度加快,且占用空间减少。Due to pre-compilation, the invocation of stored procedures is fast and has a low footprint.

    • 定序: 有时,操作需要通过某个触发机制来对数据执行一项或多项更新。Sequencing: Sometimes operations need a triggering mechanism that may perform one or additional updates to the data. 除了原子性以外,在服务器端执行还可以带来性能优势。In addition to Atomicity, there are also performance benefits when executing on the server side.

  • 封装: 使用存储过程可在一个位置分组逻辑。Encapsulation: Stored procedures can be used to group logic in one place. 封装在数据的顶层添加一个抽象层,使你能够独立于数据改进应用程序。Encapsulation adds an abstraction layer on top of the data, which enables you to evolve your applications independently from the data. 如果数据无架构,并且你无需管理直接将其他逻辑添加到应用程序的过程,则此抽象层非常有用。This layer of abstraction is helpful when the data is schema-less and you don't have to manage adding additional logic directly into your application. 借助这种抽象,可以通过从脚本简化访问来保证数据的安全。The abstraction lets your keep the data secure by streamlining the access from the scripts.

提示

存储过程最适合写入密集型操作,需要跨分区键值进行事务处理。Stored procedures are best suited for operations that are write-heavy and require a transaction across a partition key value. 确定是否使用存储过程时,请在尽量封装最大量的写入操作的基础上进行优化。When deciding whether to use stored procedures, optimize around encapsulating the maximum amount of writes possible. 一般而言,存储过程不是执行大量读取或查询操作的最有效方式,因此,使用存储过程来批处理要返回到客户端的大量读取操作不会带来所需的优势。Generally speaking, stored procedures are not the most efficient means for doing large numbers of read or query operations, so using stored procedures to batch large numbers of reads to return to the client will not yield the desired benefit. 为了获得最佳性能,应该使用 Cosmos SDK 在客户端完成这些读取密集型操作。For best performance, these read-heavy operations should be done on the client-side, using the Cosmos SDK.

事务Transactions

典型数据库中的事务可以定义为一系列作为单个逻辑单元工作执行的操作。Transaction in a typical database can be defined as a sequence of operations performed as a single logical unit of work. 每个事务提供 ACID 属性保证Each transaction provides ACID property guarantees. ACID 是一个众所周知的缩写词,表示:原子性、一致性、隔离性和持久性。 ACID is a well-known acronym that stands for: Atomicity, Consistency, Isolation, and Durability.

  • 原子性保证将一个事务内部执行的所有操作视为一个单位,这些操作要么全部提交,要么都不提交。Atomicity guarantees that all the operations done inside a transaction are treated as a single unit, and either all of them are committed or none of them are.

  • 一致性确保数据始终在各个事务之间处于有效状态。Consistency makes sure that the data is always in a valid state across transactions.

  • 隔离性保证不会存在两个事务互相干扰的情况 - 许多商务系统提供多个可以基于应用程序需求使用的隔离级别。Isolation guarantees that no two transactions interfere with each other - many commercial systems provide multiple isolation levels that can be used based on the application needs.

  • 持久性确保数据库中提交的任何更改始终存在。Durability ensures that any change that is committed in a database will always be present.

在 Azure Cosmos DB 中,JavaScript 运行时托管在数据库引擎内部。In Azure Cosmos DB, JavaScript runtime is hosted inside the database engine. 因此,在存储过程和触发器中发出的请求将在与数据库会话相同的范围内执行。Hence, requests made within the stored procedures and the triggers execute in the same scope as the database session. 此功能使 Azure Cosmos DB 能够保证所有属于某个存储过程或触发器的操作的 ACID 属性。This feature enables Azure Cosmos DB to guarantee ACID properties for all operations that are part of a stored procedure or a trigger. 有关示例,请参阅如何实现事务一文。For examples, see how to implement transactions article.

事务的范围Scope of a transaction

如果存储过程与某个 Azure Cosmos 容器相关联,则存储过程将在逻辑分区键的事务范围内执行。If a stored procedure is associated with an Azure Cosmos container, then the stored procedure is executed in the transaction scope of a logical partition key. 每个存储过程执行必须包含一个对应于事务范围的逻辑分区键值。Each stored procedure execution must include a logical partition key value that corresponds to the scope of the transaction. 有关详细信息,请参阅 Azure Cosmos DB 分区一文。For more information, see Azure Cosmos DB partitioning article.

提交和回滚Commit and rollback

事务原生集成到 Azure Cosmos DB JavaScript 编程模型中。Transactions are natively integrated into the Azure Cosmos DB JavaScript programming model. 在 JavaScript 函数内,所有操作自动包装在单个事务下。Within a JavaScript function, all the operations are automatically wrapped under a single transaction. 如果存储过程中的 JavaScript 逻辑完成且未引发任何异常,则事务中的所有操作都将提交到数据库。If the JavaScript logic in a stored procedure completes without any exceptions, all the operations within the transaction are committed to the database. BEGIN TRANSACTIONCOMMIT TRANSACTION 等语句(与关系数据库相关)在 Azure Cosmos DB 中是隐式的。Statements like BEGIN TRANSACTION and COMMIT TRANSACTION (familiar to relational databases) are implicit in Azure Cosmos DB. 如果脚本引发了任何异常,Azure Cosmos DB JavaScript 运行时将回滚整个事务。If there are any exceptions from the script, the Azure Cosmos DB JavaScript runtime will roll back the entire transaction. 在这种情况下,引发异常实际上等同于 Azure Cosmos DB 中的 ROLLBACK TRANSACTIONAs such, throwing an exception is effectively equivalent to a ROLLBACK TRANSACTION in Azure Cosmos DB.

数据一致性Data consistency

存储过程和触发器始终在 Azure Cosmos 容器的主要副本上执行。Stored procedures and triggers are always executed on the primary replica of an Azure Cosmos container. 此功能可确保从存储过程执行的读取提供非常一致性This feature ensures that reads from stored procedures offer strong consistency. 使用用户定义的函数的查询可以在主要副本或任何辅助副本上执行。Queries using user-defined functions can be executed on the primary or any secondary replica. 存储过程和触发器旨在支持事务写入 - 同时,最好是将只读逻辑实现为应用程序端逻辑,使用 Azure Cosmos DB SQL API SDK 的查询有助于最大程度地利用数据库吞吐量。Stored procedures and triggers are intended to support transactional writes - meanwhile read-only logic is best implemented as application-side logic and queries using the Azure Cosmos DB SQL API SDKs, will help you saturate the database throughput.

绑定的执行Bounded execution

所有 Azure Cosmos DB 操作必须在指定的超时持续时间内完成。All Azure Cosmos DB operations must complete within the specified timeout duration. 此约束适用于 JavaScript 函数 - 存储过程、触发器和用户定义的函数。This constraint applies to JavaScript functions - stored procedures, triggers, and user-defined functions. 如果某个操作未在该时间限制内完成,事务将会回滚。If an operation does not complete within that time limit, the transaction is rolled back.

可以确保 JavaScript 函数在时间限制内完成,或者实施一个基于延续的模型来批处理/恢复执行。You can either ensure that your JavaScript functions finish within the time limit or implement a continuation-based model to batch/resume execution. 为了简化存储过程和触发器的开发以应对时间限制,Azure Cosmos 容器下的所有函数(例如,项的创建、读取、更新和删除)将返回表示该操作是否完成的布尔值。In order to simplify development of stored procedures and triggers to handle time limits, all functions under the Azure Cosmos container (for example, create, read, update, and delete of items) return a boolean value that represents whether that operation will complete. 如果此值为 false,则表示过程必须结束执行,因为脚本占用的时间或预配吞吐量超过了配置的值。If this value is false, it is an indication that the procedure must wrap up execution because the script is consuming more time or provisioned throughput than the configured value. 如果存储过程及时完成且没有任何更多请求在排队的话,将保证完成排在第一个拒绝存储操作之前的操作。Operations queued prior to the first unaccepted store operation are guaranteed to complete if the stored procedure completes in time and does not queue any more requests. 因此,应该使用 JavaScript 的回调约定管理脚本的控制流,以将操作逐个排队。Thus, operations should be queued one at a time by using JavaScript's callback convention to manage the script's control flow. 由于脚本在服务器端环境中执行,因此受到严格的调控。Because scripts are executed in a server-side environment, they are strictly governed. 反复违反执行边界的脚本可标记为非活动状态且不可执行,应根据执行边界重新创建它们。Scripts that repeatedly violate execution boundaries may be marked inactive and can't be executed, and they should be recreated to honor the execution boundaries.

JavaScript 函数还有预配的吞吐量容量方面的约束。JavaScript functions are also subject to provisioned throughput capacity. JavaScript 函数可能会在短时间内占用大量的请求单位,如果达到预配的吞吐量容量限制,它们可能受到速率限制。JavaScript functions could potentially end up using a large number of request units within a short time and may be rate-limited if the provisioned throughput capacity limit is reached. 必须注意,脚本除了在执行数据库操作时需要消耗吞吐量以外,还会消耗其他吞吐量,不过,与从客户端执行相同的操作相比,这些数据库操作的开销略低。It is important to note that scripts consume additional throughput in addition to the throughput spent executing database operations, although these database operations are slightly less expensive than executing the same operations from the client.

触发器Triggers

Azure Cosmos DB 支持两种触发器类型:Azure Cosmos DB supports two types of triggers:

前触发器Pre-triggers

Azure Cosmos DB 提供可以通过对 Azure Cosmos 项执行操作来调用的触发器。Azure Cosmos DB provides triggers that can be invoked by performing an operation on an Azure Cosmos item. 例如,可以在创建项时指定前触发器。For example, you can specify a pre-trigger when you are creating an item. 在这种情况下,前触发器将在创建项之前运行。In this case, the pre-trigger will run before the item is created. 预触发器不能有任何输入参数。Pre-triggers cannot have any input parameters. 如果需要,可以使用请求对象更新原始请求中的文档正文。If necessary, the request object can be used to update the document body from original request. 当注册触发器后,用户可以指定随触发器一起运行的操作。When triggers are registered, users can specify the operations that it can run with. 如果使用 TriggerOperation.Create 创建了触发器,则不允许在替换操作中使用该触发器。If a trigger was created with TriggerOperation.Create, this means using the trigger in a replace operation will not be permitted. 有关示例,请参阅如何编写触发器一文。For examples, see How to write triggers article.

后触发器Post-triggers

类似于前触发器,后触发器也与针对 Azure Cosmos 项执行的操作相关联,但它们不需要任何输入参数。Similar to pre-triggers, post-triggers, are also associated with an operation on an Azure Cosmos item and they don't require any input parameters. 后触发器在操作完成之后运行,且具有对发送到客户端的响应消息的访问权限。 They run after the operation has completed and have access to the response message that is sent to the client. 有关示例,请参阅如何编写触发器一文。For examples, see How to write triggers article.

备注

注册的触发器不会在出现相应的操作(创建/删除/替换/更新)时自动运行。Registered triggers don't run automatically when their corresponding operations (create / delete / replace / update) happen. 在执行这些操作时,必须显式调用它们。They have to be explicitly called when executing these operations. 若要了解详细信息,请参阅如何运行触发器一文。To learn more, see how to run triggers article.

用户定义的函数User-defined functions

使用用户定义的函数 (UDF) 可以轻松扩展 SQL API 查询语言语法和实现自定义业务逻辑。User-defined functions (UDFs) are used to extend the SQL API query language syntax and implement custom business logic easily. 只能在查询中调用 UDF。They can be called only within queries. UDF 对上下文对象没有访问权限,旨在用作仅限计算的 JavaScript。UDFs do not have access to the context object and are meant to be used as compute only JavaScript. 因此,UDF 可以在次要副本上运行。Therefore, UDFs can be run on secondary replicas. 有关示例,请参阅如何编写用户定义的函数一文。For examples, see How to write user-defined functions article.

JavaScript 语言集成的查询 APIJavaScript language-integrated query API

除了使用 SQL API 查询语法发出查询以外,还可以借助服务器端 SDK 使用 JavaScript 接口来执行查询,使用此方法不需要 SQL 相关的知识。In addition to issuing queries using SQL API query syntax, the server-side SDK allows you to perform queries by using a JavaScript interface without any knowledge of SQL. 使用 JavaScript 查询 API 可以通过将谓词函数传入函数调用,来以编程方式生成查询。The JavaScript query API allows you to programmatically build queries by passing predicate functions into sequence of function calls. 查询将由 JavaScript 运行时分析,并在 Azure Cosmos DB 内部有效执行。Queries are parsed by the JavaScript runtime and are executed efficiently within Azure Cosmos DB. 若要了解 JavaScript 查询 API 的支持,请参阅使用 JavaScript 语言集成式查询 API 一文。To learn about JavaScript query API support, see Working with JavaScript language integrated query API article. 有关示例,请参阅如何使用 Javascript 查询 API 编写存储过程和触发器一文。For examples, see How to write stored procedures and triggers using Javascript Query API article.

后续步骤Next steps

通过以下文章了解如何在 Azure Cosmos DB 中编写和使用存储过程、触发器与用户定义的函数:Learn how to write and use stored procedures, triggers, and user-defined functions in Azure Cosmos DB with the following articles: