Database

• Разъяснение по CAP-теореме
• NoSQL базы данных: понимаем суть
• Почему не все так просто с MongoDB

• RocksDB сервер – быстрое key-value хранилище для SSD накопителей
• Deferred Integrity Constraints, Deferred Integrity Constraints in Oracle8 – this feature allows to insert child rows prior to parent rows while integrity is checked on commit.
• Хранение иерархических структур. Симбиоз «Closure Table» и «Adjacency List»
• Лучшие подходы к управлению версиями баз данных
• Бесплатные инструменты для разработчиков баз данных

1. Atomicity — операция либо целиком выполняется полностью изменяя данные, либо отменяется и не оставляет за собой следов.
2. Consistency (integrity) — после того как приложение выполняет операцию ее результат становится доступным для всех последующих операций.
3. Isolation — операции одних пользователей не имеют побочных эффектов на других пользователей.
4. Durability — изменения сделанные в результате успешной опрации сохраняют результат даже в случае системного сбоя.

The isolation level measures concurrent transactions' capacity to view data that have been updated, but not yet committed, by another transaction. If other transactions were allowed to read data that are as-yet uncommitted, those transactions could end up with inconsistent data were the transaction to roll back, or end up waiting unnecessarily were the transaction to commit successfully.

Depending on isolation level the following phenomena are permissible¹⁾:

• Lost update
  Two transactions both update a row and then the second transaction aborts, causing both changes to be lost. This occurs in systems that don’t implement any locking. The concurrent transactions aren’t isolated.
• Dirty read
  One transaction reads changes made by another transaction that hasn’t yet been committed. This is very dangerous, because those changes might later be rolled back.
• Unrepeatable read
  A transaction reads a row twice and reads different state each time. For example, another transaction may have written to the row, and committed, between the two reads.
• Second lost updates problem
  A special case of an unrepeatable read. Imagine that two concurrent transactions both read a row, one writes to it and commits, and then the second writes to it and commits. The changes made by the first writer are lost.
• Phantom read
  A transaction executes a query twice, and the second result set includes rows that weren’t visible in the first result set. (It need not necessarily be exactly the same query.) This situation is caused by another transaction inserting new rows between the execution of the two queries.

A higher isolation level means less concurrence and a greater likelihood of performance bottlenecks, but also a decreased chance of reading inconsistent data. A good rule of thumb is to use the highest isolation level that yields an acceptable performance level. The following are common isolation levels, arranged from lowest to highest:

• Read uncommitted: Data that have been updated but not yet committed by a transaction may be read by other transactions.
  On this level no locks are possible, as the data is available for reading just after modification. Rolling back the transaction will, however, revert the modifications.
  Permits dirty reads but not lost updates. One transaction may not write to a row if another uncommitted transaction has already written to it. Any transaction may read any row, however. This isolation level may be implemented using exclusive write locks.
• Read committed: Only data that have been committed by a transaction can be read by other transactions.
  On this level as well as next level the transaction can lock the data via SELECT … FOR UPDATE explicitly. Updated and removed rows are implicitly locked.
  Permits unrepeatable reads but not dirty reads. This may be achieved using momentary shared read locks and exclusive write locks. Reading transactions don’t block other transactions from accessing a row. However, an uncommitted writing transaction blocks all other transactions from accessing the row.

• Repeatable read: Only data that have been committed by a transaction can be read by other transactions, and multiple reads will yield the same result as long as the current transaction has not been committed.
  Permits neither unrepeatable reads nor dirty reads. Phantom reads may occur. This may be achieved using shared read locks and exclusive write locks. Reading transactions block writing transactions (but not other reading transactions), and writing transactions block all other transactions.
• Serializable: This, the highest possible isolation level, ensures a transaction's exclusive read-write access to data. It includes the conditions of read committed and repeatable read and stipulates that all transactions run serially to achieve maximum data integrity. This yields the slowest performance and least concurrency.
  On this level all SELECT statements will lock the matched rows for writing (e.g. SELECT … LOCK IN SHARE MODE), so another transaction can still read the data, but not modify it.
  Provides the strictest transaction isolation. It emulates serial transaction execution, as if transactions had been executed one after another, serially, rather than concurrently. Serializability may not be implemented using only row-level locks; there must be another mechanism that prevents a newly inserted row from becoming visible to a transaction that has already executed a query that would return the row.

DB2 drawbacks:

• Does not support UNSIGNED numeric types.
• Does not support DATETIME (non-SQL standard). Use TIMESTAMP instead.
• Does not support TINYINT (non-SQL standard) type.
• Does not support BOOLEAN type, thus boolean type should be emulated on number type and queries should be re-written (where A and B will not work, could be WHERE A <> 0 AND B <> 0).
• Does not support indexes on varchar greater then 1024 (otherwise SQLSTATE 54008 is returned).
• Varchar stores in bytes, thus there is a string overfit/truncation issue.
• Error reporting is not user-friendly unless message error codes are resolved.
• Row locks issues. See How to avoid deadlocks.
• Manual table page size management. If accumulated size of columns exceeds 4K, then table page size should be increased (see Choosing a page size).
• Claims to support /* */-comments (check SQL comments), but it turned out to be a feature of SQL-console.
• CREATE PROCEDURE and CREATE TRIGGER statements should end with @ delimiter, and not with ; like other SQL statements may.

• InnoDB cluster — оно работает, и вроде бы именно так, как обещали
• Миграция с SQL Server на MariaDB / MySQL. Что делать с XML

• Как я победил в конкурсе BigData от Beeline
• Open Source Conference — Big Data, Cloud, Mobile and Social strategies
• Параллельные алгоритмы для обработки BigData: подводные камни и непростые решения
• Big Data головного мозга – обзор продуктов ASF, которые продают под соусом Big Data (Storm, Sqoop и т.п.), МРР-движков поверх HDFS (Impala, Dill, Kudu), традиционных аналитических MPP-систем (Teradata, Vertica, GPDB и т.п.).

Apache Lucene / Elasticsearch:

• Conditional indexing. Оптимизируем процесс полнотекстового поиска
• Как мы Elasticsearch готовили, или О том, как обработать 36 тысяч логов в секунду
• Поиск по Postgres с использованием ZomboDb и ElasticSearch

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"
    p:driverClass="com.mysql.jdbc.Driver"
    p:jdbcUrl="jdbc:mysql://host:3306/my_db?autoReconnect=true"
    p:user="john"
    p:password="john_pass"
    p:maxPoolSize="20"
    p:maxStatements="50"
    p:testConnectionOnCheckin="true"
    p:idleConnectionTestPeriod="7200"
    p:preferredTestQuery="SELECT 1"
/>

<bean id="dataSource" class="org.apache.tomcat.jdbc.pool.DataSource" destroy-method="close"
    p:driverClassName="org.hsqldb.jdbcDriver"
    p:url="jdbc:hsqldb:mem:test"
    p:username="sa"
    p:password=""
    p:maxActive="20"
    p:testOnReturn="true"
    p:testWhileIdle="true"
    p:validationInterval="1800000"
    p:validationQuery="SELECT 1 FROM information_schema.tables"
/>

<?xml version="1.0" encoding="UTF-8"?>
<beans
    xmlns="http://www.springframework.org/schema/beans"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans        http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
        http://www.springframework.org/schema/jdbc        http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd">
 
    <jdbc:embedded-database id="dataSource" />
</beans>

<?xml version="1.0" encoding="UTF-8"?>
<beans
    xmlns="http://www.springframework.org/schema/beans"
    xmlns:jee="http://www.springframework.org/schema/jee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans        http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
        http://www.springframework.org/schema/jee        http://www.springframework.org/schema/jee/spring-jee-3.1.xsd">
 
    <jee:jndi-lookup id="dataSource" jndi-name="java:jdbc/myDS" />
</beans>