Editor’s note: The video platform Vimeo leverages managed database services from Google Cloud to serve up billions of views around the world each day. Here’s how they deliver a consistent user experience across all their content.

Vimeo is the world’s leading video software solution, powering hundreds of thousands of new uploads every day and billions of views. At our scale, it’s paramount that we retain a seamless user experience across all of the videos hosted, shared, and viewed on our platform. With managed database services from Google Cloud, we’ve been able to deliver a consistent and reliable user experience no matter where our users and their audiences are.

https://youtube.com/watch?v=ggpAUgEL2iE%3Fenablejsapi%3D1%26

Outgrowing the data center and gaining more scale

Our first exposure to Google Cloud was the excellent performance between Google Compute Engine (GCE) and Google Cloud Storage (GCS), which we used to design a really high quality playback and upload environment for our customers’ videos. At first we were simply creating cloud-based packaging and delivery of their content, but the bulk of our application ran on an on-premises MySQL database. Seeing the gains from our initial investment in GCE and GCS, we decided to fully migrate to Google Cloud. 

We started by lifting and shifting our workloads from our on-premises deployments to GCE. But we quickly realized we had to redesign our application to deliver higher availability and responsiveness for an exceptional user experience, which led us to Cloud Spanner, a distributed SQL database management and storage service. With Spanner, we found all the benefits of a relational semantics database with global scale, allowing us to add more nodes with the push of a button. 

Today we have a multi-region instance with 99.999% uptime, so we’re confident it can handle our video workloads. We use about 16 Spanner nodes to cover approximately 50.8 billion rows and about 4.5 terabytes of storage/disk space. And with the launch of Video Library, we have the scale we need to fundamentally change the way organizations handle content. 

Using Spanner as a time machine during migration

During the initial migration, we wanted to ensure an accurate data transfer with as few errors or bugs as possible. When we moved data from MySQL tables into Spanner, we ran spot analyses to check for potential problems. If the data didn’t match, Spanner allowed us to revert to the original in a single click. Through Spanner we could check stale timestamps or past users. It almost functioned like a time machine by offering point-in-time recovery functionality.

Serving up any video at any time

We work with a long tail of content. Depending on how popular a video is, we take a different approach to storing it. We cache the most popular material so it’s immediately accessible and use Cloud Spanner to ensure it’s highly available across multiple regions. On the flip side, we use Cloud SQL for some lower volume satellite services that support other components of the application that don’t require the same guarantees or performance that Cloud Spanner provides.  

As our millions of users continue to upload their own videos, Spanner also acts as a video metadata indexing and cataloging service. When we need to play back a particular video, we can quickly and easily find it in a sea of content. It’s crucial that our database is highly available and reliable so the playback experience is seamless for our users. 

To learn more about Vimeo, visit our site.

Learn more about how your organization can use Cloud Spanner.

Cloud Spanner is a fully managed relational database with unlimited scale, strong consistency, and up to 99.999% availability. It is designed for highly concurrent applications that read and update data, for example, to process payments or for online game play. To ensure the consistency across multiple concurrent transactions, Cloud Spanner uses a combination of shared locks and exclusive locks to control access to the data. In this blog, we will explore the different types of locks present in Cloud Spanner. We will also discuss some common cases of transactional locking in Cloud Spanner, and what to look out for to detect when these cases might be occurring.

What is a lock?

Before we get into the details, let us first quickly recap and define a lock in the context of database systems.

“Locks” in databases are a mechanism for concurrency control. Locks are typically held on a resource, which may mean rows, columns, tables or even entire databases. When a resource is locked by a transaction, it cannot be accessed by another transaction until the lock is released.

Timeline view of transactions

Before we proceed to discuss transaction locking in the context of read and read-write transactions, it is important to recap the timeline view of the transactions in Spanner.

Please also keep in mind the concept of write buffering in transactions, since we refer to it in the Common Transaction Patterns section below. Write buffering refers to the Cloud Spanner server(s) accepting the writes. Note that these writes are not durable until a commit has been performed.

Types of locks in Cloud Spanner

Cloud Spanner operations acquire locks when the operations are part of a read-write transaction. Read-only transactions do not acquire locks. Unlike other approaches that lock entire tables or rows, the granularity of transactional locks in Spanner is a cell, or the intersection of a row and a column. This means that two transactions can read and modify different columns of the same row at the same time. To maximize the number of transactions that have access to a particular data cell at a given time, Cloud Spanner uses different lock modes.

Here is a brief description of the different lock types. Learn more about each lock type in the Cloud Spanner documentation.

1. ReaderShared Lock – Acquired when a read-write transaction reads data.
2. WriterShared Lock – Acquired when a read-write transaction writes data without reading it.
3. Exclusive Lock – Acquired when a read-write transaction which has already acquired a ReaderShared lock tries to write data after the completion of read. It is a special case for a transaction to hold both the ReaderShared lock and WriterShared lock at the same time.
4. WriterSharedTimestamp Lock – Special type of lock acquired when inserting new rows with the transaction’s commit timestamp as part of the primary key.

Handling Lock Conflicts

Since read-write transactions use locks to execute atomically, they run the risk of deadlocking. For example, consider the following scenario: transaction Txn1 holds a lock on record A and is waiting for a lock on record B, and Txn2 holds a lock on record B and is waiting for a lock on record A. The only way to make progress in this situation is to abort one of the transactions so it releases its lock, allowing the other transaction to proceed.

Cloud Spanner uses the standard “wound-wait” algorithm to handle deadlock detection. Under the hood, Spanner keeps track of the age of each transaction that requests conflicting locks. It also allows older transactions to abort younger transactions, where “older” means that the transaction’s earliest read, query, or commit happened sooner.

Common Transaction Patterns

Armed with the basics of transactions and locks in Cloud Spanner, we will now walk through a few practical use-cases. We take the example of an application which queries and updates users’ balance in a table named accounts. The accounts table has the following columns –

Case 1: Transaction waiting to get exclusive lock because of higher priority shared-lock

Sequence

1. Txn1 begins.
2. Txn2 begins.
3. Txn2 reads the table and acquires a ReadShared Lock.
4. Txn1 buffers its write.
5. Txn1 tries to commit, but since Txn2 has higher priority (because it has executed its first operation first), Txn1 will have to wait until Txn2 releases the ReadShared Lock.

After committing, Txn2 releases its ReadShared Lock. Txn1 is now able to upgrade to an Exclusive Lock. It acquires the Exclusive Lock and commits.

Note 1: UPDATE WHERE is always transformed into SELECT WHERE, so an UPDATE statement is not a blind write, but a read-write operation.

Note 2: While the above example considers the use-case of querying and updating a single row, the same transaction pattern is also applicable across a key-range.

What to watch out for

• Timeouts due to Transactions waiting for acquiring locks. Refer to transaction statistics and lock statistics to understand how to detect this.

When does this typically happen

• Concurrent updates to a single key/key-range in a table.

Case 2: Transaction aborted because of concurrent execution succeeding

Sequence

1. Txn1 begins.
2. Txn2 begins.
3. Txn1 reads a row from the table and acquires a ReadShared Lock.
4. Txn2 reads the same row as Txn1 and acquires a ReadShared Lock.
5. Txn1 buffers its write.
6. Txn2 buffers its write.
7. Txn1 tries to commit, has higher priority (because it has executed its first operation first), it will get priority in upgrading to an Exclusive Lock. It acquires the Exclusive Lock and commits.

Since Txn1 has committed and was the higher priority transaction, Txn1 will abort Txn2.. The abort will likely happen before Txn2 tries to commit.

What to watch out for

• High number of transaction aborts due to wounding of transactions. Refer to transaction statistics and lock statistics to understand how to detect this.

When does this typically happen

• Concurrent updates to a single key/key-range. Typically this happens in the case of hot keys being present in the table.

Case 3: Transaction waiting to get exclusive lock because of higher priority shared-lock & getting aborted because of prior succeeding concurrent execution

Sequence

1. Txn1 begins.
2. Txn2 begins.
3. Txn2 reads the table and acquires a ReadShared Lock.
4. Txn1 reads the table and acquires a ReadShared Lock.
5. Txn1 buffers its write.
6. Txn2 buffers its write.
7. Txn1 tries to commit, but since Txn2 holds a ReadShared Lock, it has to wait until it gets cleared.
8. Since Txn2 has higher priority (because it has executed its first operation first), it will acquire the Exclusive Lock first. Txn2 upgrades its ReaderShared Lock to an Exclusive Lock and commits.

Finally, after Step 8, when Txn1 tries to commit (since ReaderShared Lock from Txn2 is now cleared) it gets aborted. This is done to prevent deadlock with the higher priority transaction (Txn2).

Note: In Case 1, even though Txn2 had acquired a ReaderShared Lock earlier as in Case 3, it never upgraded to an Exclusive Lock (since there were no writes). Hence there was no need to abort Txn1, it could simply wait for Txn2 to release its ReaderShared lock and then commit.

What to watch out for

• High number of transaction aborts due to high contention between transactions. Refer to transaction statistics and lock statistics to understand how to detect this.

When does this typically happen

• Concurrent updates to a single key/key-range. Typically this happens in the case of hot keys being present in the table.

Recommendations

In order to mitigate these issues and reduce their occurrence. We recommend adopting the following best practices:

• If you need to perform more than one read at the same timestamp, and know in advance that you only need to read, consider using a read-only transaction. Because read-only transactions don’t write, they don’t hold locks and they don’t block other transactions. Further, read-only transactions never abort, so you don’t need to wrap them in retry loops.
• Always acquire ReadShared Locks on the smallest subset of keys or key ranges. This reduces the chances of lock contention.
• Analyze your code to only include critical path code within a transaction. Avoiding unneeded remote calls or complex, long-running business logic is a good way to ensure a transaction process quickly.
• Analyze your needs for multi-split transactions. Since transactions that update more than one split use a 2-phase commit protocol, they hold locks for a longer duration, thereby increasing chances of lock contention.

Get started today

Spanner’s unique architecture allows it to scale horizontally without compromising on the consistency guarantees that developers rely on in modern relational databases. Try out Spanner today for free for 90 days or for as low as $65 USD per month.

Today, we are excited to announce the general availability of Dataplex data lineage — a fully managed Dataplex capability that helps you understand how data is sourced and transformed within the organization. Dataplex data lineage automatically tracks data movement across BigQuery, BigLake, Cloud Data Fusion (Preview), and Cloud Composer (Preview), eliminating operational hassles around manual curation of lineage metadata.

With rising data volume spread across data silos, it can be challenging for organizations to ensure users have a self-service mechanism to discover, understand and trust the data. Organizations constantly struggle with questions such as:

• Is the data extracted from an authoritative source?
• What is the impact if I drop this table?
• The data in this table seems corrupted – where did this data come from, and when was it last refreshed?
• How is sensitive information being moved or copied? Is it in adherence to data governance practices?

To answer the above questions, organizations need to track how data is sourced and transformed, which can be complex and requires significant effort.

Dataplex data lineage describes each lineage relationship by detailing what happened and when it happened in an interactable lineage graph, providing data observability.

Data analysts who want to know if a table originates from an authoritative source can now answer this in a self-service manner with a simple look-up of lineage for the concerned table — available in Dataplex and in BigQuery for in-context analysis.

Data engineers can reduce time to identify and resolve data issues through root cause analysis using the operational metadata trace asserting a lineage relationship. Data lineage also aids deterministic change management by providing the ability to evaluate the impact of a change and collaborate with the corresponding stakeholders to minimize any adverse impact.

Finally, data lineage provides a map of data movement which can become the foundation for data governance practice. It enables data stewards and owners to evaluate and enforce adherence to governance requirements, especially when tracking the movement of sensitive information.

Dataplex data lineage provides APIs for extensibility so that organizations can report lineage from various systems and have a single map of how data entries are related.

What our customers are saying

L’Oréal, the world’s largest cosmetics company, is on a mission to ‘create the beauty that moves the world.’ “Dataplex data lineage helps us understand how data moves across our organization,” said Sébastien Morand, Head of Data Engineering team, L’Oréal. “As a fully managed solution, it becomes the main entry point to diagnose data issues and evaluate the impact of a change or incident — providing insight on what happened and when it happened, including reference to the execution metadata. Directly integrated into our beauty tech data platform, data lineage helps us reduce data issues and also enables us to mitigate issues faster when it does happen.”

“At Wayfair, we treat data-as-a-product and are building a robust data platform that provides self-service access and compliance constructs,” said Vinit Rajopadhye, Associate Director on Data Infrastructure & Data Enablement at Wayfair. “We are excited about Dataplex data lineage as it helps our data consumers trust data based on where it originates and the transformations applied.”

Hurb is an online travel agency in Brazil with a mission to optimize travel through technology. “Hurb has a rapidly growing data platform, with new data assets created and registered daily to support business decision-making and Machine Learning models,” said Vinícius dos Santos Mello, Senior Data Engineer. “Thanks to Dataplex data lineage features, we have end-to-end data observability across data in BigQuery. We can proactively address schema changes, data quality issues, and asset depreciation that could otherwise negatively affect the business.”

“As a company with many business domains and services, we handle a large volume of data and use it to power our decision making, so it is crucial to ensure data quality. Dataplex data lineage provides a visual understanding of the flow of data across our organization, improving efficiency of impact investigations when problems occur and increasing the reliability of the data.” said Mitsunori Fukase, Data Platform Department Group Manager, DeNA.

Get started with Dataplex data lineage

You can get started with Dataplex data lineage by enabling the Data Lineage API on your project. You can learn more here.

Additional Resources:

• Dataplex data lineage labs
• Quickstart – track lineage for BigQuery table copy