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 of reading and writing data from a database table, includes: (1) initiating a write transaction to write data to a first non-key column of a row of the database table, the database table having a plurality of rows, each row comprising a primary key and a plurality of non-key columns; (2) locking the first non-key column; and (3) in accordance with a determination that the second non-key column is not locked, initiating a read transaction to read data from the second non-key column, where initiation of the read transaction occurs prior to completion of the write transaction.
1. A method of reading and writing data from a database table, comprising:
attempting a write transaction to write data to a first item in a row of the database table, the database table having a plurality of rows, each row comprising a plurality of items, wherein each item is associated with information that indicates when the last write occurred for the item;
determining whether the write for the first item is blocked based at least on the information;
when the write for the first item is determined to be blocked, preventing the write for the first item without preventing writes to the other items in the row; and
when the write for the first item is determined to not be blocked, committing the write for the first item.
2. The method of claim 1 , wherein each of the plurality of items in the row is a column in the row.
3. The method of claim 1 , wherein the information associated with each item of the row comprises a write timestamp for each item.
4. The method of claim 3 , wherein, for each item of the row, write timestamps for the item increase monotonically with successive write transactions.
5. The method of claim 4 , wherein a user of the database table is prohibited from editing data corresponding to a prior write timestamp.
6. The method of claim 1 , further comprising initiating a read transaction to read data from a second item of the row, wherein initiation of the read transaction occurs prior to completion of the write transaction.
7. The method of claim 6 , wherein reading the data from the second item includes reading data corresponding to a read timestamp that is:
greater than the last-write timestamp of the second item; and
less than a value designated as the next new write timestamp for the database table.
8. The method of claim 7 , wherein the read transaction includes reading a plurality of items, with each of the plurality of items distinct from the first item, and wherein the read timestamp is greater than the last-write timestamp for each of the plurality of items.
9. The method of claim 1 , wherein each row of the database table comprises one or more shards, and the shards for each row form a partition of the items in the respective row.
10. The method of claim 9 , wherein the first item and a second item are in the same shard of the first row.
11. A database management system, comprising one or more server computers, each server computer including:
one or more processors;
memory;
a database table stored in the memory, the database table having a plurality of rows, each row comprising a plurality of items, wherein each item is associated with information that indicates when a last write occurred for the item; and
one or more programs stored in the memory for execution by the one or more processors, the one or more programs comprising instructions for:
attempting a write transaction to write data to a first item of a row of the database table;
determining whether the write for the first item is blocked based at least on the information;
when the write for the first item is determined to be blocked, preventing the write for the first item without preventing writes to the other items in the row; and
when the write for the first item is determined to not be blocked, committing the write for the first item.
12. The database management system of claim 11 , wherein each of the plurality of items in the row is a column in the row.
13. The database management system of claim 11 , wherein the information associated with each item of the row comprises a write timestamp for each item.
14. The database management system of claim 13 , wherein, for each item of the row, write timestamps for the item increase monotonically with successive write transactions.
15. The database management system of claim 14 , wherein a user of the database table is prohibited from editing data corresponding to a prior write timestamp.
16. The database management system of claim 11 , further comprising initiating a read transaction to read data from a second item of the row, wherein initiation of the read transaction occurs prior to completion of the write transaction.
17. The database management system of claim 16 , wherein reading the data from the second item includes reading data corresponding to a read timestamp that is:
greater than the last-write timestamp of the second item; and
less than a value designated as the next new write timestamp for the database table.
18. The database management system of claim 17 , wherein the read transaction includes reading a plurality of items, with each of the plurality of items distinct from the first item, and wherein the read timestamp is greater than the last-write timestamp for each of the plurality of items.
19. A non-transitory computer-readable storage medium storing one or more programs configured for execution by one or more server computers, each server computer having one or more processors and memory storing one or more programs for execution by the one or more processors, the one or more programs comprising instructions for:
maintaining a database table, the database table having a plurality of rows, each row comprising a plurality of items, wherein each item is associated with information that indicates when a last write occurred for the item;
attempting a write transaction to write data to a first item of a row of the database table;
determining whether the write for the first item is blocked based at least on the information;
when the write for the first item is determined to be blocked, preventing the write for the first item without preventing writes to the other items in the row; and
when the write for the first item is determined to not be blocked, committing the write for the first item.
20. The non-transitory computer-readable storage medium of claim 19 , wherein each of the plurality of items in the row is a column in the row.