Systems and methods for increasing database access concurrency
The various embodiments described herein include methods, devices, and systems for reading and writing data from a database table. In one aspect, a method includes: (1) initiating a read transaction to read from a first non-key column of a row in the database table, the database table having a plurality of rows, each row comprising a primary key and a plurality of non-key columns, the initiating including: (a) determining that a write transaction holds a lock on a second non-key column of the row in the database table, and (b) determining that no lock is held on the first non-key column; and (2) in response, concurrently reading data from the first non-key column and writing a new column value to the second non-key column; where each non-key column includes a last-write timestamp that indicates when the last write occurred for the respective non-key column.
1. A method of maintaining concurrency, comprising:
initiating a write transaction for data in a first partition of a database table, the database table having a plurality of rows;
locking, with one or more processors, the first partition without locking the entire row;
receiving, with one or more processors, a read request for data in a second partition of the database table, the second partition being distinct from the first partition, the second partition having a last-write timestamp that indicates when the last write occurred for the second partition, the read request associated with a read timestamp selected to be greater than the last-write timestamp of the first partition and less than a minimum next new write timestamp for the database table; and
reading, with one or more processors, the second partition while the write transaction holds the lock on the first partition, wherein reading the second partition is performed prior to completion of the write transaction for the first partition and comprises selecting a value from the second partition corresponding to the read timestamp.
2. The method of claim 1 , further comprising prohibiting editing of objects associated with timestamps less than the last-write timestamp.
3. The method of claim 1 , wherein the write transaction is received at a database replica, and wherein the database replica independently serves the write transaction without having to communicate with other database servers.
4. The method of claim 1 , wherein the first partition comprises a plurality of columns.
5. A system for maintaining concurrency, comprising:
memory storing a database table having a plurality of partitions; and
one or more processors in communication with the memory, the one or more processor configured to:
initiate a write transaction for data in a first partition of the database table, the database table having a plurality of rows;
lock the first partition without locking the entire row;
receive a read request for data in a second partition of the database table, the second partition being distinct from the first partition, the second partition having a last-write timestamp that indicates when the last write occurred for the second partition, the read request associated with a read timestamp selected to be greater than the last-write timestamp of the first partition and less than a minimum next new write timestamp for the database table; and
read the second partition while the write transaction holds the lock on the first partition, wherein reading the second partition is performed prior to completion of the write transaction for the first partition and comprises selecting a value from the second partition corresponding to the read timestamp.
6. The system of claim 5 , wherein the one or more processors are further configured to prohibit editing of objects associated with timestamps less than the last-write timestamp.
7. The system of claim 5 , wherein the write transaction is received at a database replica, and wherein the database replica independently serves the write transaction without having to communicate with other database servers.
8. The system of claim 5 , wherein the first partition comprises a plurality of columns.