Transcript database concurrency - Wildcard Conference

Public
© Tieto Corporation
Shared Database Concurrency
Aivars Kalvāns
Lead Software Architect
Tieto
[email protected]
Public
Who am I
• Working at “Tieto Cards” > 11 years
• Junior developer → Lead Software Architect
• C and C++, Python
• Oracle database
• C++ library for working with Oracle OCI
• MongoDB for Developers certificate
2
© Tieto Corporation
2013-09-09
Public
About Tieto Cards
• Payment card systems
•
•
•
•
3
Both on-line and batch processing
Linux, AIX, HP-UX, Solaris, Windows
Oracle Tuxedo middleware (C++)
Oracle database (PL/SQL, PRO*C, OCI)
© Tieto Corporation
2013-09-09
Public
Largest customers
•
•
•
•
31,000,000 cards
1,000,000,000 financial transactions/year
160,000 POS terminals
3,700 ATM
• 250 financial transactions/second on-line
• More for batch processing: 24h data within 8h
• Thousands in case of spherical cards in a vacuum
4
© Tieto Corporation
2013-09-09
Public
Terms
•
•
•
•
5
Concurrency
Consistency
Locks
Blocking
© Tieto Corporation
2013-09-09
Public
Shared memory concurrency
Concurrent components communicate by altering the contents
of shared memory locations
• Threads (C++, Java, C#, …)
• Processes (Oracle on Unix)
• Popular topic at developer conferences
• Lock-free
• Actor model, …
6
© Tieto Corporation
2013-09-09
Public
Shared database concurrency
Concurrent components communicate by altering the contents
of shared memory locations
Concurrent components communicate by altering shared data
in a database
7
© Tieto Corporation
2013-09-09
Public
Why is it important
• Concurrency in application server →
Concurrency in database server
• Database is the bottleneck
• Mixing functionality of batch and online processing
• Design for concurrency
• Hard to fix afterwards
8
© Tieto Corporation
2013-09-09
Public
Concurrently shared data
• Accounts and limits
• Card
• Merchant
• Bank
• Real-time statistics for fraud detection
• Country
• Merchant category (hotel, casino, escort service)
9
© Tieto Corporation
2013-09-09
Public
Locking in a database
• Pessimistic locking and Optimistic locking
• http://www.orafaq.com/papers/locking.pdf
More challenging than the technology is overcoming resistance
from seasoned development professionals who have been
using the trusted SELECT… FOR UPDATE for all of their
Oracle careers.
These individuals may need to be convinced of the benefits of
using optimistic and on large development projects their
support will be crucial.
10
© Tieto Corporation
2013-09-09
Public
What has been tried
• Use the best practices, architecture, patterns for the
application
• Leave database concurrency to optimistic locking
11
© Tieto Corporation
2013-09-09
Public
• Does not work as expected*
• Acceptable until x transactions per second
• System gets slower when concurrency increases
• Optimistic = hope nobody modifies the same data
* for our use case, your experience may differ
12
© Tieto Corporation
2013-09-09
Public
Oracle 101
• Row-level (TX) locks
• LOCK TABLE …
• Locks are held until COMMIT or ROLLBACK
• Not in case of ROLLBACK TO SAVEPOINT
• Writes don’t block reads
• Reads don’t block writes
13
© Tieto Corporation
2013-09-09
Public
Placing a row-level lock
• UPDATE, DELETE, SELECT … FOR UDATE [NOWAIT]
• INSERT
• Primary key or unique constraint violation
• Every modification implies locking
• To guarantee ACID properties
• Let’s call it “implicit locking”
14
© Tieto Corporation
2013-09-09
Public
Few years later…
15
© Tieto Corporation
2013-09-09
Public
Pessimistic
Optimistic
t1
SELECT …
FOR UPDATE
SELECT version, …
t2
Modify data
Modify data
t3
UPDATE …
UPDATE
SET version = :next_version
WHERE version = :known_version
LOCK
Time
t4
t5
16
COMMIT
© Tieto Corporation
Retry if ROWCOUNT=0
COMMIT
2013-09-09
LOCK
Pessimistic vs. Optimistic
Public
“Implicit”
Optimistic
t1
SELECT …
SELECT version, …
t2
Modify data
Modify data
t3
UPDATE …
UPDATE
SET version = :next_version
WHERE version = :known_version
LOCK
Time
t4
t5
COMMIT
Retry if ROWCOUNT=0
COMMIT
• Additional consistency control!
17
© Tieto Corporation
2013-09-09
LOCK
“Implicit” vs. Optimistic
Public
When to control consistency?
There are 10 kinds of updates
18
Don’t need it
Need consistency
Relative
update
balance = balance + :deposit
balance = balance - :withdraw
Absolute
update
name = :new_name
balance = :new_balance
© Tieto Corporation
2013-09-09
Public
Conclusions about locking
• If you don’t have concurrent updates on the same data
• Does not matter which locking you use
• Every modification places row-level locks
• If you do have concurrent updates on the same data
• There is nothing “optimistic” about optimistic locking in Oracle
database
• Pessimistic is better
• Optimistic locking burns more cycles due to retries
19
© Tieto Corporation
2013-09-09
Public
How to choose locking
• “Implicit locking” by default
• Relative updates
• Last update wins for absolute updates
• Pessimistic locking
• To prevent concurrency
• Optimistic locking
• Stateless applications
• “Conditional consistency”
20
© Tieto Corporation
2013-09-09
Public
How we have chosen locking
• Cards – pessimistic locking
• Historic reasons, unlikely concurrent updates
• Statistics – “implicit locking”
• Accounts
• Optimistic locking for “conditional consistency”
• Ensure consistency sometimes
• for withdrawals and complex interest calculations
• “Implicit locking” otherwise
• …and few more tricks
21
© Tieto Corporation
2013-09-09
Public
How to increase concurrency
Same as with shared memory concurrency
• Reduce the time locks are held
• Reduce lock granularity
Not an option
• Lock-less …
• NoSQL
22
© Tieto Corporation
2013-09-09
Public
Reduce (b)locking time
• Do as little work as possible between locking DML and
COMMIT
• Reorder to perform locking DML last
• UPDATE, INSERT, COMMIT
vs.
• INSERT, UPDATE, COMMIT
• Up to 50% improvement
• Not all DML have equal possibility of blocking
• Update card account – 10 times / month
• Update bank account – 10 times / second
23
© Tieto Corporation
2013-09-09
Public
Increase performance
• Bulk operations
• Multiple rows with one DML
• SELECT … WHERE id IN (:id1, :id2, :id3)
• ROWID within transaction boundaries
• Physical address of the row
• Index maps values to ROWIDs
• Except:
• Index-Organized Tables
• Table management (shrink, flashback, partition key change)
• SELECT ROWID, …
• UPDATE / DELETE … WHERE ROWID = :rowid
24
© Tieto Corporation
2013-09-09
Public
Increase performance
• Faster servers
• Faster CPU (memory, disk) → shorter locking time
• Few fast CPUs are better than many slow CPUs
• Locking time can be reduced by faster not more processes
25
© Tieto Corporation
2013-09-09
Public
Do more with less DML
• INSERT … RETURNING … INTO …
• UPDATE and DELETE as well
• INSERT ALL … / INSERT WHEN …
• MERGE …
• USING (SELECT … FROM DUAL)
26
© Tieto Corporation
2013-09-09
Public
Release locks faster
• Avoid distributed transactions
• Often too easy to use
• Two-phase commit: prepare and commit
• Extra step and coordination overhead before locks are released
Processes
TPS / Distributed
TPS / Local
4
836
1070 (+28%)
8
972
1260 (+29%)
• COMMIT on DML success
• DML+COMMIT in single round-trip
• INSERT, COMMIT vs. INSERT+COMMIT – 10% improvement
• Up to 50% improvement in case of blocking
27
© Tieto Corporation
2013-09-09
Public
Reduce lock granularity
• “Partition” rows
• Multiple rows instead of one
• Update row based on random or hash
• Aggregate rows when reading
28
id
balance
partition
id
balance
13
15
0
13
42
13
7
1
13
11
2
13
9
3
© Tieto Corporation
2013-09-09
Public
Reduce lock granularity
• INSERT instead of UPDATE
• Create a change journal
• INSERT offsets
• Aggregate and UPDATE once in a while
29
© Tieto Corporation
2013-09-09
30
Public
© Tieto Corporation
Thank you
and
a happy programmers’ day!
2013-09-09
Public
© Tieto Corporation
Aivars Kalvāns
Lead Software Architect
Tieto
[email protected]