Efficient Identity Columns Allocation in Sybase ASE Cluster Edition

摘要

In modern computing systems, unique identifiers are often associated with system generated objects to clearly identify an object to the system and distinguish any one object from others in the system. In database systems, unique identifiers are used to assign unique identifiers to rows in a table (popularly known as ROWIDs or RIDs). In Sybase Adaptive Server Enterprise (ASE), the method to assign a unique ROWID for a row in a table is to create a column in the table with "identity" property. Every time a row is inserted in the table, the value for the "identity" column is generated internally, is independent of the location of the row in the underlying storage, and is guaranteed to be different from all the previously generated values for that table. ASE Cluster Edition (ASE CE) refers to the ASE version that runs on a hardware shared disk cluster, where all the clustered servers have direct access to the set of database devices and jointly manage the single installation of the databases. In a distributed database system like ASE CE, all the nodes can insert rows in parallel in a table having "identity" column. To ensure a unique value for every row, all the inserts would have to synchronize on a global distributed lock, which becomes the bottleneck in achieving good throughputs. Synchronizing on a global lock for inserts also leads to heavy messaging in the distributed system, and indirectly affects the overall performance of the system. In this paper, we tackle the above two critical problems faced by any distributed database system while inserting rows in parallel from different nodes. We introduce a fully distributed identifier caching strategy which achieves high insert performance and reduced messaging. Our solutions have been introduced in ASE Cluster Edition and could be easily applied in any distributed system.