You are here: Architecture > Transaction Support
   |
   |
|
Transaction Support
|
|
|
|
ScimoreDB uses full ACID transactions. To start a longer, multi-query transaction, you can initiate a transaction by:
Concurrency control is available through specifying the transaction isolation level (in the SCDriver client and the Database Manager's query window connection properties) and by specifying locks (XLOCK TABLE, XLOCK ROW and READ CONSISTENT) in table sources of DML queries.
If one transaction is writing to a table and another is reading, the concurrency controls will determine whether the read transaction will use the data that exists before the writing transaction or it will wait until write transactions are committed before reading the updated result.
Transaction Isolation Levels
Read Committed
Includes any committed changes made to the data read while the transaction is in progress in the final result.
Read Repeatable
Reads only data that was already committed before the start of the transaction. Even if data is updated while a read transaction is in progress, the reading transaction will see data as it was before the writing took place. That is, consecutive reading queries will always return the same result in the same transaction, regardless of other writing transactions.
Serialized
Only allow one transaction at one time. Any new transactions starting while a seperate serialized transaction will wait until serialized one is finished.
Lock Types
Shared Lock
When a transaction requires read or write access to a table, a shared lock is automatically created. Multiple shared locks can exist on a table from multiple transactions. A shared lock will wait if another transaction has an exclusive lock on the table. All DML queries will create a shared lock on the tables they operate on.
Exclusive Row Lock
INSERT, UPDATE, and DELETE automatically use row level exclusive locks on rows they update/insert/delete. When a transaction has a exclusive row lock, no other transaction can access the locked row for write. If another transaction tries to access the same row while using READ CONSISTENT, XLOCK ROW or XLOCK TABLE it will wait for the current exclusive row lock to be released. DML queries using exclusive locks in their table sources will wait for other exclusive locks to be released.
Exclusive row locks can be specified for table sources, (eg. from SELECT) using XLOCK ROW.
Exclusive Table Lock
When a transaction has a exclusive lock on a table, no other transaction can access it for read or write. All DDL queries and TRUNCATE will first abort any transactions with shared locks, before creating an exclusive lock on tables they operate on.
Exclusive table locks can be specified for DML commands' table sources using XLOCK TABLE.
DDL is Transactional
Unlike most other SQL databases, ScimoreDB's transaction model includes DDL statements. This is necessary for the distributed nature of the database.
This also has perks for the developer, as you can atomically CREATE DATABASEs, CREATE TABLEs, INSERT, UPDATE, SELECT, etc and then safely abort with ROLLBACK. Another way this could be useful is for testing a planned ALTER TABLE query.
See the ALTER TABLE page for examples.
Transaction performance
Disk IO speed is the main factor for the database performance. Since, it is relatively slow to compare with other resources, for example CPU.
ScimoreDB attempts to cache the most frequent used database fragments in a memory, such as index or data pages. Therefore, setting larger DB cache size will always benefit the performance.
Write transactions modify database data files (in-memory) and performs a log write to transaction log file. Therefore, the speed of those transactions will be limited to disk write speed to transaction log. To increase transactional throughput, ScimoreDB uses group-commit to attempt to decrease file system write operations. Group-commit means that concurrent transactions can combine their log data to a single batch and issue a single disk write operation, instead of, each transaction writes its own log data to disk. It also means, that more concurrent transactions trying to commit, the database has a better chance to group log to a single batch.
Another way to increase write transaction speed is to enable transaction log buffering using SET LOG FLUSH . So, each transaction will write log data to memory prior to a disk. Then checkpoint process (default 5 minutes) will flush buffered data to the transaction log file. There is a risk of losing, since the last checkpoint, database changes, if DB terminates unexpectedly.
Related