cancel
Showing results for 
Search instead for 
Did you mean: 
Administration & Architecture
Explore discussions on Databricks administration, deployment strategies, and architectural best practices. Connect with administrators and architects to optimize your Databricks environment for performance, scalability, and security.
cancel
Showing results for 
Search instead for 
Did you mean: 

What happens when multiple people write to the same Delta table?

Anonymous
Not applicable
 
1 ACCEPTED SOLUTION

Accepted Solutions

Srikanth_Gupta_
Valued Contributor

Delta supports ACID transactions and guarantees between reads and writes.

Delta uses Optimistic concurrency control mechanism to handle concurrent transactions, there are two types of locking mechanism.

Pessimistic concurrency control -> it assumes that two or more users will want to update the same record at the same time, and then prevents that possibility by locking the record, no matter how unlikely conflicts actually are.The locks are placed as soon as any piece of the row is accessed, making it impossible for two or more users to update the row at the same time. Depending on the lock mode (shared, exclusive, or update), other users might be able to read the data even though a lock has been placed. For more details on the lock modes, see Lock modes: shared, exclusive, and update.

Optimistic concurrency control -> Instead of locking every record every time that it is used, the system merely looks for indications that two users actually did try to update the same record at the same time. If that evidence is found, then one user’s updates are discarded and the user is informed.

To use an analogy with banks, pessimistic locking is like having a guard at the bank door who checks your account number when you try to enter; if someone else (a spouse, or a merchant to whom you wrote a check) is already in the bank accessing your account, then you cannot enter until that other person finishes her transaction and leaves. Optimistic locking, on the other hand, allows you to walk into the bank at any time and try to do your business, but at the risk that as you are walking out the door the bank guard will tell you that your transaction conflicted with someone else’s and you will have to go back and do the transaction again.

more details are available here w.r.t Delta

https://docs.databricks.com/delta/concurrency-control.html#conflict-exceptions

https://docs.databricks.com/delta/optimizations/isolation-level.html

View solution in original post

3 REPLIES 3

Ryan_Chynoweth
Honored Contributor III

Delta enforces an optimistic concurrency control. Check out our documentation page here.

Srikanth_Gupta_
Valued Contributor

Delta supports ACID transactions and guarantees between reads and writes.

Delta uses Optimistic concurrency control mechanism to handle concurrent transactions, there are two types of locking mechanism.

Pessimistic concurrency control -> it assumes that two or more users will want to update the same record at the same time, and then prevents that possibility by locking the record, no matter how unlikely conflicts actually are.The locks are placed as soon as any piece of the row is accessed, making it impossible for two or more users to update the row at the same time. Depending on the lock mode (shared, exclusive, or update), other users might be able to read the data even though a lock has been placed. For more details on the lock modes, see Lock modes: shared, exclusive, and update.

Optimistic concurrency control -> Instead of locking every record every time that it is used, the system merely looks for indications that two users actually did try to update the same record at the same time. If that evidence is found, then one user’s updates are discarded and the user is informed.

To use an analogy with banks, pessimistic locking is like having a guard at the bank door who checks your account number when you try to enter; if someone else (a spouse, or a merchant to whom you wrote a check) is already in the bank accessing your account, then you cannot enter until that other person finishes her transaction and leaves. Optimistic locking, on the other hand, allows you to walk into the bank at any time and try to do your business, but at the risk that as you are walking out the door the bank guard will tell you that your transaction conflicted with someone else’s and you will have to go back and do the transaction again.

more details are available here w.r.t Delta

https://docs.databricks.com/delta/concurrency-control.html#conflict-exceptions

https://docs.databricks.com/delta/optimizations/isolation-level.html

BigRoux
New Contributor III
New Contributor III

Multiple writers (inserts/appends) present no problems with Delta. You can have two users appending data to a Delta table at the same time without issue. Updates, deletes, merges, and compaction can run into issues when multiple user are trying to do these operations at the same time.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!