{"id":30878,"date":"2019-10-31T21:37:56","date_gmt":"2019-10-31T18:37:56","guid":{"rendered":"https:\/\/prohoster.info\/blog\/tranzaktsii-i-mehanizmy-ih-kontrolya\/"},"modified":"2019-10-31T21:37:56","modified_gmt":"2019-10-31T18:37:56","slug":"tranzaktsii-i-mehanizmy-ih-kontrolya","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/tranzaktsii-i-mehanizmy-ih-kontrolya","title":{"rendered":"Transactions and mechanisms for their control","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Transactions<\/h2>\n

<\/p>\n

A transaction is a sequence of operations on data that has a beginning and an end.<\/h4>\n

\nA transaction is a sequential execution of read and write operations. The end of a transaction can either be the saving of changes (commit) or the cancellation of changes (rollback). In the context of databases, a transaction is several queries that are treated as a single query.<\/p>\n

Transactions must satisfy ACID properties.<\/h4>\n

\nAtomicity. A transaction is either fully executed or not executed at all.<\/p>\n

Consistency. Upon completion of a transaction, constraints imposed on the data (such as constraints in a database) must not be violated. Consistency implies that the system will be transitioned from one valid state to another valid state.<\/p>\n

Isolation. Concurrently executed transactions should not affect each other, for example, by changing data that another transaction is using. The result of executing concurrent transactions should be as if the transactions were executed sequentially.<\/p>\n

Durability. After a commit, changes should not be lost.
\n<\/a><\/noindex><\/p>\n

Transaction log<\/h2>\n

<\/p>\n

The log stores changes made by transactions and ensures the atomicity and durability of data in the event of a system failure.<\/h4>\n

\nThe log contains the values that data had before and after changing by the transaction. The write-ahead log strategy requires adding a record of previous values before the start and of final values after the completion of the transaction. In case of a sudden system shutdown, the database reads the log in reverse order and undoes changes made by transactions. Upon encountering an interrupted transaction, the database executes it and records its changes in the log. When in a state at the time of failure, the database reads the log in the forward order and restores changes made by transactions. This preserves the durability of transactions that have already been committed and the atomicity of the interrupted transaction.<\/p>\n

Simply re-executing erroneous transactions is not sufficient for recovery. <\/p>\n

Example. A user has $500 in their account and decides to withdraw it through an ATM. Two transactions occur. The first checks the balance, and if there are sufficient funds, it dispenses money to the user. The second deducts the required amount from the balance. Suppose a system failure occurs, the first transaction does not complete, but the second does. In this case, we cannot reissue money to the user without restoring the system to its original state with a positive balance.<\/i><\/p>\n

Isolation Levels<\/h2>\n

<\/p>\n

Read Committed<\/h4>\n

\nThe Dirty Read problem occurs when a transaction can read an intermediate result produced by another transaction.<\/p>\n

Example. The initial balance is $0. T1 adds $50 to the balance. T2 reads the balance (which is $50). T1 rolls back the changes and completes. T2 continues to execute with the incorrect balance data.<\/i><\/p>\n

The solution is Read Committed, which prevents reading data modified by a transaction. If transaction A has modified a certain set of data, transaction B must wait for transaction A to complete before accessing that data.<\/p>\n

Repeatable Read<\/h4>\n

\nThe Lost Updates problem occurs when T1 saves changes over T2's changes.<\/p>\n

Example. The initial balance is $0, and two transactions simultaneously increase the balance. T1 and T2 read the balance as $0. Then T2 adds $200 to $0 and saves the result. T1 adds $100 to $0 and saves the result. The final result is $100 instead of $300.<\/i><\/p>\n

The Unrepeatable Read problem occurs when rereading the same data returns different values.<\/p>\n

Example. T1 reads the balance, which is $0. Then T2 adds $50 to the balance and completes. T1 rereads the data and finds a discrepancy with the previous result.<\/i><\/p>\n

Repeatable Read guarantees that rereading will return the same result. Data read by one transaction is prohibited from being changed by others until the transaction is complete. If transaction A reads a certain set of data, transaction B must wait for transaction A to complete before accessing that data.<\/p>\n

Serializable<\/h4>\n

\nPhantom Read Problem. Two queries that select data based on certain conditions return different values.<\/p>\n

Example. T1 requests the number of all users whose balance is greater than $0 but less than $100. T2 deducts $1 from a user with a balance of $101. T1 executes the query again.<\/i><\/p>\n

Ordered Read (Serializable). Transactions are executed as fully sequential. Updating and adding records that fall under the conditions of the request is prohibited. If transaction A requests data from the entire table, the table is frozen for other transactions until transaction A is completed.<\/p>\n

Scheduler<\/h2>\n

<\/p>\n

Determines the order in which operations should be executed during concurrently running transactions.<\/h4>\n

\nProvides a specified level of isolation. If the result of operations does not depend on their order, such operations are commutative (Permutable). Read operations and operations on different data are commutative. Read-write and write-write operations are not commutative. The scheduler's task is to interleave operations performed by concurrent transactions so that the result is equivalent to the sequential execution of transactions.<\/p>\n

Concurrency Control Mechanisms<\/h2>\n

<\/p>\n

Optimistic is based on detecting and resolving conflicts, pessimistic on preventing conflicts from occurring.<\/h4>\n

\nIn the optimistic approach, multiple users receive copies of the data. The first to finish editing saves the changes, while the others must merge the changes. The optimistic algorithm allows conflicts to occur, but the system must recover after a conflict.<\/p>\n

In the pessimistic approach, the first user to acquire the data prevents others from accessing it. If conflicts are rare, it is reasonable to choose the optimistic strategy, as it provides a higher level of parallelism.<\/p>\n

Locking<\/h2>\n

<\/p>\n

If one transaction locks the data, other transactions must wait for the data to be unlocked.<\/h4>\n

\nA lock can be applied to a database, table, row, or attribute. A Shared Lock can be held on the same data by multiple transactions, allowing all transactions (including the one that placed the lock) to read but prohibiting modifications and exclusive locks. An Exclusive Lock can only be held by one transaction, allowing that transaction to perform any actions while prohibiting actions by others.<\/p>\n

Deadlock is a situation where transactions are stuck waiting indefinitely.<\/h4>\n

\nFor example, the first transaction waits for data held by the second, while the second waits for data held by the first.<\/i><\/p>\n

An optimistic approach to resolving deadlocks allows the deadlock to occur but then restores the system by rolling back one of the transactions involved in the deadlock.<\/h4>\n

\nDeadlocks are checked for periodically. One detection method is time-based, meaning a deadlock is presumed to have occurred if a transaction is taking too long to execute. When a deadlock is found, one of the transactions is rolled back, allowing other transactions involved in the deadlock to complete. The choice of which transaction to roll back may be based on the cost of the transactions or their age (Wait-Die and Wound-Wait schemes). <\/p>\n

Each transaction T<\/b> is assigned a timestamp TS<\/b> indicating the time the transaction started executing.<\/p>\n

Wait-Die. <\/p>\n

If TS(Ti)<\/b> < TS(Tj)<\/b>, then Ti<\/b> waits, otherwise Ti<\/b> it rolls back and restarts with the same timestamp.<\/u><\/p>\n

If a younger transaction has acquired a resource and an older transaction requests the same resource, the older transaction is allowed to wait. If the older transaction has acquired the resource, the younger transaction requesting that resource will be rolled back.<\/p>\n

Wound-Wait. <\/p>\n

If TS(Ti)<\/b> < TS(Tj)<\/b>, then Tj<\/b> is rolled back and restarts with the same timestamp, otherwise Ti<\/b> it waits.<\/u><\/p>\n

If a younger transaction acquires a resource while an older transaction requests the same resource, the younger transaction will be rolled back. If an older transaction has acquired the resource, the younger transaction requesting that resource is allowed to wait. The choice of victim based on age prevents deadlocks but can roll back transactions that are not in a deadlock state. The problem is that transactions can be rolled back many times, as an older transaction may hold the resource for a long time.<\/p>\n

The pessimistic solution to the deadlock problem does not allow a transaction to start execution if there is a risk of a deadlock occurring.<\/h4>\n

\nTo detect a deadlock, a graph (wait-for graph) is constructed where the nodes are transactions and the edges are directed from transactions waiting for data release to the transactions that have acquired that data. A deadlock is considered to have occurred if the graph has cycles. Building the wait-for graph, especially in distributed databases, is an expensive procedure.<\/p>\n

Two-phase locking prevents deadlocks by acquiring all resources used by a transaction at the beginning of the transaction and releasing them at the end.<\/h4>\n

\nAll blocking operations must precede the first unlocking operation. It has two phases \u2014 the Growing Phase, during which acquisitions occur, and the Shrinking Phase, during which releases occur. If it is impossible to acquire one of the resources, the transaction starts over. There can be a situation where a transaction cannot acquire the required resources, for example, if multiple transactions compete for the same resources.<\/p>\n

Two-phase commit ensures that the commit is executed on all database replicas.<\/h4>\n

\nEach database logs information about the data that will be changed and responds OK to the coordinator (Voting Phase). Once all have responded OK, the coordinator sends a signal requiring everyone to commit. After the commit, server<\/a> they reply OK; if even one does not respond OK, then the coordinator sends a cancel signal to all servers (Completion Phase).<\/p>\n

Timestamp method<\/h2>\n

<\/p>\n

An older transaction is rolled back when it attempts to access data involved in a younger transaction.<\/h4>\n

\nEach transaction is assigned a timestamp TS<\/b> corresponding to the time it begins execution. If Ti<\/b> older than Tj<\/b>, then TS(Ti)<\/b> < TS(Tj)<\/b>.<\/p>\n

When a transaction rolls back, it is assigned a new timestamp. Each data object Q<\/b> involved in the transaction is marked with two timestamps. W-TS(Q)<\/b> \u2014 the timestamp of the youngest transaction that successfully performed a write on Q<\/b>. R-TS(Q)<\/b> \u2014 the timestamp of the youngest transaction that performed a read write on Q<\/b>.<\/p>\n

When the transaction T<\/b> requests to read data Q<\/b> two scenarios are possible.<\/p>\n

If TS(T)<\/b> < W-TS(Q)<\/b>, meaning that the data has been updated by a younger transaction, the transaction T<\/b> rolls back.<\/u><\/p>\n

If TS(T)<\/b> >= W-TS(Q)<\/b>, then the read is executed and R-TS(Q)<\/b> becomes MAX(R-TS(Q), TS(T))<\/b>.<\/u><\/p>\n

When the transaction T<\/b> requests to change the data Q<\/b> two scenarios are possible. <\/p>\n

If TS(T)<\/b> < R-TS(Q)<\/b>, meaning that the data has already been read by a younger transaction and if a change is made, a conflict will occur. The transaction T<\/b> rolls back. <\/u><\/p>\n

If TS(T)<\/b> < W-TS(Q)<\/b>, meaning the transaction is trying to overwrite a newer value, transaction T rolls back. In other cases, the change is executed and W-TS(Q)<\/b> is set to TS(T)<\/b>.<\/u><\/p>\n

There is no need for costly wait-for graph construction. Older transactions depend on newer ones, hence there are no cycles in the wait-for graph. No deadlocks occur since transactions do not wait, but roll back immediately. Cascading rollbacks are possible. If Ti<\/b> has rolled back, and Tj<\/b> read data that it modified Ti<\/b>, then Tj<\/b> must also roll back. If during this time Tj<\/b> had already been committed, then it leads to a violation of the stability principle.<\/p>\n

One solution for cascading rollbacks. The transaction performs all write operations at the end, and other transactions must wait for the completion of this operation. Transactions wait for a commit before reading.<\/p>\n

Thomas write rule \u2014 a variation of the timestamp method in which data updated by a younger transaction cannot be overwritten by an older one.<\/h4>\n

\u0422\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0438 \u0422\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0435\u0439 \u043d\u0430\u0437\u044b\u0432\u0430\u0435\u0442\u0441\u044f \u043f\u043e\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u0439 \u043d\u0430\u0434 \u0434\u0430\u043d\u043d\u044b\u043c\u0438 \u0438\u043c\u0435\u044e\u0449\u0430\u044f \u043d\u0430\u0447\u0430\u043b\u043e \u0438 \u043a\u043e\u043d\u0435\u0446 \u0422\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u044f \u044d\u0442\u043e \u043f\u043e\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u0442\u0435\u043b\u044c\u043d\u043e\u0435 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u0435 \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u0439 \u0447\u0442\u0435\u043d\u0438\u044f \u0438 \u0437\u0430\u043f\u0438\u0441\u0438. \u041e\u043a\u043e\u043d\u0447\u0430\u043d\u0438\u0435\u043c \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0438 \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u043b\u0438\u0431\u043e \u0441\u043e\u0445\u0440\u0430\u043d\u0435\u043d\u0438\u0435 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0439 (\u0444\u0438\u043a\u0441\u0430\u0446\u0438\u044f, commit) \u043b\u0438\u0431\u043e \u043e\u0442\u043c\u0435\u043d\u0430 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u0438\u0439 (\u043e\u0442\u043a\u0430\u0442, rollback). \u041f\u0440\u0438\u043c\u0435\u043d\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u043a \u0411\u0414 \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u044f \u044d\u0442\u043e \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u0438\u0445 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0442\u0440\u0430\u043a\u0442\u0443\u044e\u0442\u0441\u044f \u043a\u0430\u043a \u0435\u0434\u0438\u043d\u044b\u0439 \u0437\u0430\u043f\u0440\u043e\u0441. \u0422\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0438 \u0434\u043e\u043b\u0436\u043d\u044b \u0443\u0434\u043e\u0432\u043b\u0435\u0442\u0432\u043e\u0440\u044f\u0442\u044c \u0441\u0432\u043e\u0439\u0441\u0442\u0432\u0430\u043c ACID \u0410\u0442\u043e\u043c\u0430\u0440\u043d\u043e\u0441\u0442\u044c. \u0422\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u044f \u043b\u0438\u0431\u043e \u0432\u044b\u043f\u043e\u043b\u043d\u044f\u0435\u0442\u0441\u044f \u043f\u043e\u043b\u043d\u043e\u0441\u0442\u044c\u044e […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-30878","post","type-post","status-publish","format-standard","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Transactions and their control mechanisms | ProHoster","description":"Transactions A transaction is a sequence of operations on data that has a beginning and an end. A transaction is the sequential execution of read and write operations.","canonical_url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/tranzaktsii-i-mehanizmy-ih-kontrolya","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0422\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0438 \u0438 \u043c\u0435\u0445\u0430\u043d\u0438\u0437\u043c\u044b \u0438\u0445 \u043a\u043e\u043d\u0442\u0440\u043e\u043b\u044f | ProHoster","og:description":"\u0422\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0438 \u0422\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u0435\u0439 \u043d\u0430\u0437\u044b\u0432\u0430\u0435\u0442\u0441\u044f \u043f\u043e\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u0439 \u043d\u0430\u0434 \u0434\u0430\u043d\u043d\u044b\u043c\u0438 \u0438\u043c\u0435\u044e\u0449\u0430\u044f \u043d\u0430\u0447\u0430\u043b\u043e \u0438 \u043a\u043e\u043d\u0435\u0446 \u0422\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u044f \u044d\u0442\u043e \u043f\u043e\u0441\u043b\u0435\u0434\u043e\u0432\u0430\u0442\u0435\u043b\u044c\u043d\u043e\u0435 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u0435 \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u0439 \u0447\u0442\u0435\u043d\u0438\u044f \u0438 \u0437\u0430\u043f\u0438\u0441\u0438.","og:url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/tranzaktsii-i-mehanizmy-ih-kontrolya","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T18:37:56+00:00","article:modified_time":"2019-10-31T18:37:56+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"30878","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-02-22 15:31:13","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-03-01 03:27:38","updated":"2026-02-22 15:31:13","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/30878","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=30878"}],"version-history":[{"count":1,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/30878\/revisions"}],"predecessor-version":[{"id":162008,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/30878\/revisions\/162008"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=30878"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=30878"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=30878"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

\nTransaction T<\/b> requests to change the data Q<\/b>. If TS(T)<\/b> < W-TS(Q)<\/b>, meaning that the transaction is trying to overwrite a newer value, transaction T does not roll back as in the timestamp method.
\n
Source: habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"