Transactions and Locking

• Chapter 21: transactions
• Chapter 22: Concurrency Control

Abstract transactions

The System Log

• start
• each write(), including what field or record was written and either the value written or the old value or both
• the final commit or abort

Sometimes the results of each read() are also included, though this has more to do with auditing than recovery.

The basic idea is that, on database restart after a crash, the first step is to search the log for transactions that have committed or aborted. The values written by those transactions can then be verified; for committed transactions the new value should be present in the actual database and for aborted ones the old value should be present. The next step is to identify those transactions that have not committed or aborted; those transactions will be rolled back using the write() information.

The ACID test

• Atomicity: either all steps of a transaction are performed, or none are. We either commit the entire thing or rollback completely.
  
• Consistency preservation: the individual transaction should preserve database consistency and database invariants
  
• Isolation: transactions should appear to be executing in isolation.
  
• Durability: committed transactions survive DB crashes
  

Transaction schedules

Conflict

1. They belong to different transactions
2. They access the same data item (eg X)
3. At least one of the operations is a write()

1. Ti executes write(X) and later Tj executes read(X)
2. Ti executes  read(X) and  later Tj executes write(X)
3. Ti executes write(X) and later Tj executes write(X)

Recoverable and nonrecoverable schedules

What happens if we create a schedule in which we do all the additions and then all the multiplications? Note that, if we defer the commits, this can be a recoverable schedule. And there are no lost updates. But it is still not equivalent to any serial schedule: we end up with X=Y=Z=121.

To run this in Postgres:

create table xyz(x integer, y integer, z integer);
insert into xyz values (100, 100, 100);

T1: begin;
update xyz set x = x+10;
update xyz set y = y*1.1;

T2: begin;
update xyz set y = y+10;
update xyz set z = z*1.1;

T3: begin;
update xyz set z = z+10;
update xyz set x = x*1.1;

One would probably also set an isolation level, with

set transaction isolation level repeatable read;

Serializability

Conflict-serializability algorithm

1. T_i executes write(X) and later T_j executes read(X)
2. T_i executes  read(X) and  later T_j executes write(X)
3. T_i executes write(X) and later T_j executes write(X)

View Serializability

A less restrictive schedule-equivalence rule is that of view equivalence. This essentially means that for each read operation on a specific variable X that appears in both of the two schedules, the same value is returned for each read. Specifically, if S and S' are the two schedules in question, and if r_i(X) appears in transaction T_i in schedule S, and the same r_i(X) appears in S', then both will read the value written by w_j(X) in earlier transaction T_j. (We also need a rule to require that S and S' have the same set of final writes.)

View serializability is similar to conflict serializability if there are no blind writes, that is, transactions that write a value without reading it. No blind writes means that if T_i has a write w_i(X), then it has an earlier r_i(X). There is also a rule that the value X written depends only on the value of X read, which is less convincing as, for example, we may be swapping X and Y.

Debit-credit transactions

Isolation Rules

• read uncommitted
• read committed
• repeatable read
• serializable

In tabular form, these allow:

A dirty read is a read by one transaction of data written by another transaction that has not yet committed. For example: T₁ reads a value written by T₂; T₂ later aborts. This could also be described as an uncommitted read.

An example of a nonrepeatable read: T₁ reads a value, and later reads the value again. In between, T₂ (which may have started earlier, but has since committed) updated the value.

A phantom read occurs when the second transaction inserts an entirely new row. For example, suppose T₁ reads a set of rows determined by a (possibly empty) WHERE clause. T₂ now adds a new row that meets the WHERE condition. If T₁ repeats the read of the rows, it gets a different set of rows.

Here is an example:

T1: select sum(salary) from employee where dno = 2;

T2: insert into employee (fname, lname, ssn, dno, salary) values ('Robert', 'Smith', '991004321', 2, 35000);

T1: select sum(salary) from employee where dno = 2;

The second time T1 executes the query, it gets a different value. However, no individual records were changed.

Sometimes a nonrepeatable read is described as one transaction reading from a write by another transaction, while a phantom read is described as one transaction reading from an append by another. That is, an update to a record represents a write, while the insertion of a new record represents an append.

The serializable category does not mean the transactions are actually conflict-serializable; it only means that none of the three read() anomalies are permitted. The serializable category prohibits not only phantom reads, but also any serialization anomaly: any end result that isn't the same as the result of executing the transactions in at least one serialized order.

Postgres and MVCC

To enforce isolation levels, Postgres implements Multi-Version Concurrency Control, or MVCC. When you update a record in Postgres, the old version isn't overwritten. Instead, it lingers, coexisting with the new version of the record. Record versions are often called tuples in Postgres; the rule is that any individual tuple is immutable; that is, is never changed. There is no such thing as an in-place field update!

Every transaction has a numeric transaction id, available with select txid_current(). Every tuple has special fields xmin and xmax. When a tuple is inserted (or updated), xmin is set to the transaction id of the inserting transaction; that is, each tuple carries an identification of the transaction that created it. Similarly, the xmax value is set when a tuple is marked as deleted. The tuple is not physically deleted, though, at least not right away, but Postgres will not show the deleted tuple to any transaction with transaction id > xmax. An older (but long-running) transaction, however, might be shown this now-deleted tuple (if the isolation level were repeatable-read or above).

The Postgres system also maintains a list of transactions that are currently pending, so the commit status can easily be determined.

When Postgres finds a record, perhaps through the index, it actually finds a linked list of tuples, starting with the most recent. We can call this the tuple chain. For a given transaction, Postgres searches the list for a tuple that has either committed or else was created by that particular transaction. Higher isolation levels add additional constraints, but the rule stated here is sufficient to enforce the Read Committed isolation rule: a transaction will simply not find tuples created by another uncommitted transaction.

Let's see this Read Committed effect in action. We'll start a new transaction T1 and insert Ralph Wiggum, as in the example at the beginning:

begin;        -- T1
insert into employee values ('Ralph', 'I', 'Wiggum', '123212321', null, null, 'M', 23000, '888665555', 1);

Now let us have the first transaction, T1, commit. The second window now sees Ralph.

Postgres Isolation Levels

Postgres uses MVCC (that is, the tuples with the xmin and xmax tags, below) to implement isolation levels above Read Committed. Here's a Postgres table:

Postgres does not allow Read Uncommitted at all. Postgres does not allow Phantom Reads with Repeatable-Read isolation, but does allow serialization anomalies. Postgres does not make a distinction between nonrepeatable reads and phantom reads.

We've already seen some of the behavior of Read Committed. Let's repeat that, with two transactions, T1 and T2 in that order. T2 will be the transaction that inserts Ralph. When T2 first inserts Ralph, the insertion is not visible to T1, as it has not committed. Nor would Ralph be visible to a third transaction, T3, that started after T2. Once T2 has committed, Ralph is visible to both T1 and T3. For Read Committed isolation, all that matters is whether the writing transaction (T2 here) has committed.

Now let's see Read Committed in action with a single update. We'll assume Ralph Wiggum has been added previously with a salary of 23000.

T1: begin;
set transaction isolation level read committed;        -- optional, as this is the default
select salary from employee where lname = 'Wiggum';

We get 23000, as expected. But T1 has not yet committed. Now we run T2; note that T2 commits right away. (This, by the way, is the same as running the update statement here without the begin/commit.)

T2: begin; update employee set salary = salary + 1000 where lname = 'Wiggum'; commit;

T1 is still running. Let's see what we read now for Ralph's salary:

T1: select salary from employee where lname = 'Wiggum';

We get 24000! That is, T1 reads T2's update.

But what if both T1 and T2 try to update Ralph's salary? We will start T1 and then T2 as before, with Ralph added, at a salary of 23000. T1 will update Ralph's salary:

update employee set salary = salary+1000 where lname='Wiggum';

Now let's have T2 do a different update:

update employee set salary = salary+2000 where lname='Wiggum';

This update simply hangs until T1 aborts or commits. This is because of what we might call the conflicting-write rule: a transaction may not insert or update a record if another uncommitted transaction has already done so. In light of this rule, there is no way for T2 to carry out this update! If T1 aborts, T2's update will change Ralph's salary to 25000; if T1 commits, T2's update will change Ralph's salary to 26000. T2 cannot update Wiggum's salary until T1 commits (or aborts). The conflicting-write rule means that T2 cannot update a value (Wiggum's salary) that is currently "in play"; that is, subject to an update by an uncommitted transaction.

Finally, let's have T2 insert into a table. First we create the table, and populate it with values 1 and 2:

create table numbers (num integer primary key);
insert into numbers values (1),(2);

To implement read-committed, to a first approximation all Postgres has to do is the following:

1. make sure that transactions don't read tuples created by as-yet-uncommitted transactions. Usually a read will find an earlier tuple on the "tuple chain", and read from that instead.
2. make sure transactions don't write to records (don't create new tuples) where there exists a tuple in the tuple chain that is currently marked as having been modified by another, still-uncommitted transaction. Writing to a different tuple is not allowed; the transaction must hang until the first transaction finishes.

Snapshots

1. The list of currently running (not yet committed) transactions
2. The highest transaction number so far

All operations restricted to the snapshot are then not allowed to read updates/inserts from transaction ids either in the list in 1 or greater than the maximum in 2. That is, an operation within a snapshot can only see changes that have committed before the snapshot was taken.

In order to do this, Postgres records for each tuple values xmin and xmax:

• xmin is the transaction id of the transaction that created the tuple
• xmax is the transaction id that deleted the tuple (if any)

When searching the tuple chain during the course of the transaction, a tuple is rejected if its transaction id is in the snapshot list of transactions in progress but not yet committed as of the start of the original transaction, or has xmin greater than the original transaction's id (there is a similar rule for delete operations and xmax).

As an example, let's look at the transaction id and the employee records this time with xmin (and xmax):

begin;
select txid_current();
insert into employee values ('Ralph', 'I', 'Wiggum', '123212321', null, null, 'M', 23000, '888665555', 1);
select fname, lname, xmin, xmax from employee;

The transaction id equals Ralph's xmin! (Ralph has no xmax, because he hasn't been deleted.)

The longer a snapshot is held, the more expensive it becomes to maintain it, in terms of overhead. For the read-committed isolation level, a separate snapshot is taken at the start of each individual query within the transaction, and lasts only until that query (for example, an update command) ends. In manual demonstrations, individual queries take a few milliseconds each and so run fully serialized (that is, there is no overlap). In this setting, the snapshot plays no obvious role. In high-density transaction environments, however, things are different.

For the simple read-committed-with-serialized-queries case, the only effective constraint is that queries within a transaction are not allowed to read tuples that have been written by other transactions that have not yet committed. The snapshot data in 1 and 2 above is ephemeral.

As we shall see, the corresponding repeatable-read rule requires that the snapshot be held for the lifetime of the transaction; that is, until the user types "commit". In this case, we can demonstrate with manual queries the snapshot behavior.

The xmin and xmax values are exactly what we need to maintain snapshots. Recall that a tuple's xmin represents the id of the transaction that inserted or updated the tuple. Any query within the lifetime of the snapshot is not shown tuples with an xmin in the list in 1, or with an xmin larger than the cutoff in 2, even if the other query creating the tuple has committed; such tuples are outside the view of the snapshot. Similarly, the transaction query is shown deleted tuples with an xmax in the list in 1, or larger than the cutoff in 2. In the eyes of the snapshot, those tuples are not yet deleted.

Most uses of snapshots also include a conflicting-write provision to prevent two transactions from writing (append or update) to the same data. So if a transaction wants to insert or update a record, and another uncommitted transaction has already done this, the second transaction must wait.

See https://momjian.us/main/writings/pgsql/mvcc.pdf.

Repeatable Read

T1: begin;
set transaction isolation level repeatable read;  
select salary from employee where lname = 'Wiggum';

Now we update Ralph's salary in a second transaction T2, as before. T2 may use the default read committed isolation level

T2: begin; update employee set salary = salary + 1000 where lname = 'Wiggum'; commit;

Does T1 see this updated salary? No!

T1: select salary from employee where lname = 'Wiggum';

This still returns 23,000, the salary in effect in the "snapshot" as of the start of T1!

Now let's have T1 commit. After this, we do see the new salary of 24,000.

Postgres implements all this by taking a snapshot of the database at the time the transaction T1 starts, and using the snapshot until the end of the transaction. This snapshot includes the transaction ids of all transactions that were still active as of the start of T1. Any updates made by those transactions (marked with xmin of their transaction id) are not allowed to be seen by T1. Similarly, updates from any transactions starting after T1 cannot be seen by T1 (That is, T1 will only be able to see changes made by transactions that committed before T1 started). The same applies to deletions, using xmax.

The other way to implement Repeatable Read isolation, not using snapshots, is to use locks; MySQL does this. Locks usually create a considerable performance bottleneck. The discovery of snapshot-based Repeatable Read isolation was a significant advance. The Postgres group was an early implementer of this approach.

Next let's do the conflicting salary updates with repeatable-read for T1. T1 starts first,

T1: begin; set transaction isolation level repeatable read;

and then T2 does the first salary update:

T2: update employee set salary = salary + 2000 where lname = 'Wiggum';

After that, but before T2 has committed (so T2 has to be a "real" transaction; we cannot rely on autocommit), T1 issues its update request:

T1: update employee set salary = salary + 1000 where lname = 'Wiggum';

Again T1 hangs, because there is already an uncommitted write to this record. If T2 aborts, T1 can finish, but not if T2 commits. If T2 does commit, T1 immediately gets the error message

ERROR:  could not serialize access due to concurrent update

The same thing happens if T2 sets salary = 25000 and T1 tries to set salary = 24000, that is, if a pure update is done rather than a read-and-update.

This is a fact of life with repeatable-read: you have to be prepared to retry a transaction.

In terms of tuples, after the T2 update we have two tuples for Ralph:

After T2 commits, the second tuple is marked as committed, but the first tuple is still part of T1's snapshot and so is still active. If T1 had not tried to update Ralph's salary, it could continue working, but it would see the salary as 23000. T1's snapshot tuple (with the 23000) goes away only when T1 commits (or aborts).

Serializable

Locking

• the locked data item D
• whether the lock is a read-lock or a write-lock
• the transaction holding the lock (if it is a write lock) or the list of transactions holding the lock (if it is a read lock)
• a list of transactions waiting on the lock

1. A lock (read or write) must be obtained on X before read(X)
2. A write lock must be obtained on X before write(X)
3. X must be unlocked after a transaction is done with X
4. Transactions should not issue multiple lock requests for the same X

These rules do not guarantee serializability. Consider the following (from fig 22.3); T1 and T2 have X and Y reversed.

If initially X=20 and Y=30, then if we run T1 we have X=50 and Y=30; running T2 then leaves us with X=50 and Y=80. Executing T2 first leaves us with Y=50; then running T1 leaves us with X=70, Y=50.

But if we execute the transaction according to the schedule above, then T2 writes Y=50, while T1 writes X=50. This is not equivalent to either serial schedule!

The problem here is that T1 unlocked Y too soon (similarly, T2 unlocks X too soon).

Two-phase locking

• conservative: transactions must lock all the items to be accessed at the start of the transaction. This is deadlock-free, but sometimes the transaction does not know every item to be read until partway through.
• strict: no write locks are released until the transaction's commit/rollback. This means that no other transaction that reads from this one can do so until the first one has committed. This leads to a strict schedule, which is then cascadeless. Deadlocks may occur.
• rigorous: no locks at all are released until commit/rollback. Rigorous two-phase locking is sort of a mirror image of conservative two-phase locking.

Generally it is the concurrency-control subsystem of the database that locks and unlocks items, and not the transactions themselves.

Deadlock

Timestamps

Deadlock Detection

Timestamps

• read_TS(X): the largest (most recent) timestamp of any transaction T that has executed read(X)
• write_TS(X): the largest timestamp of any transaction that has executed write(X)

Now, if transaction T attempts read(X), the algorithm compares TS(T) with read_TS(X). If read_TS(X) is newer (larger), then T is aborted and rescheduled later with a larger timestamp. If write_TS(X) > TS(T), then we drop its write(X) operation, on the theory that X would be overwritten anyway by the transaction T₂ with TS(T₂) = write_TS(X). This looks disturbing, but an actual read-write conflict would have been detected with the read_TS(X) case.

MyISAM v InnoDB

• better support for ACID transactions
• log-based crash recovery (below)
• "hot" backup support (backups while the DB is running)
• More engine-tuning options

Indexes and Locking

If a B-tree is accessed with the goal of inserting a record, a naive write lock would apply to the entire structure, tying up the root node in particular until the lock was released. A more careful strategy is to create special lock protocols for indexes. Locks on upper levels of the index can be released as soon as it is clear that those levels will not be written to, and that whatever goes on at lower levels won't affect upper levels.

This is not completely trivial: insertion of a leaf node into a B-tree can indeed create a new root node. However, if we can determine that there is a node with sufficient room for insertion, then any sequence of "value push-ups" will go no higher, and the upper parts of the tree can be unlocked.

Crash Recovery

• a transaction ID
• original values of the data fields to be updated
• a flag indicating that the transaction is pending

Transactions per second

What is a good number? The pgbench tool can be used to run Postgres benchmarks. To initialize, create a database named pgbench and then, as system user postgres, run "pgbench -i pgbench". When I then run

    pgbench -c 25 -j 25 -t 400 pgbench    # 25 threads, 400 transactions per thread

I get (in 2024)

    tps = 1250

That's one transaction in 0.8 ms.

What about, say, Amazon? The tps rate at Amazon isn't quite as high as one might think, though Amazon doesn't like to publish figures. In 2016, Amazon hit 600 items per second. In 2017, during the post-Thanksgiving five-day sales period they hit maybe 200 million items in 120 hours, for about 400-500 items/second. Other sources suggest 300 transactions per second for a peak rate.

My transaction demo

We will simulate multiple users buying things online by creating multiple Python threads to perform transactions, and measuring the transactions per second.

• customer(custid serial, custname varchar(20))
• part (partnum serial, price decimal, quan_in_stock integer)
• invoice (invnum serial, ordertime timestamp, custid integer, totalamt decimal)
• invitem(invoicenum integer, partnum integer, quantity integer)

Here is my typical transaction, generated by my Python script below, and based on input custid, part1, quan1, part2, quan2:

begin;
insert into invoice(ordertime, custid) values (now(), custid);
insert into invitem values (invnum, part1, quan1);
update part set quan_in_stock = quan_in_stock - quan1 where partnum = part1;
insert into invitem values (invnum, part2, quan2);
update part set quan_in_stock = quan_in_stock - quan2 where partnum = part2;  
...
commit;

Actual transactions have ten items, making ten parts updates (rather than the two parts updates shown above)

The transaction "insert into invoice" is not the problem, unless the same customer places dozens of orders per second. The "insert into invitem" is also generally safe, as there should never be more than one transaction operating on a given invnum (why?). It's the "update part" that is the bottleneck issue, as this must lock that record of the part table.

Here are the files:

• The Python script conndemo1.py
• A later variant conndemo3.py, using partupdate (below)
• The SQL to create the tables setup.sql
• Some records: customer.csv and part.csv

Between experiments we really should clean out the invoice and invitem tables, but that can be complicated due to foreign-key constraints. Start with invitem and partupdate; then delete from invoice. It does not make a significant difference.

These experiments are done with either MAXPART = 50 (lots of contention, as everyone is ordering the same parts), or 500 (less contention).

Occasionally we may need to restock:

    update part set quan_in_stock = 1000;

Experiment 1: true serial execution

Run conndemo.py with NUMTHREADS=1, PERTHREAD=1000. (We don't need to enable isolation level serializable, as we're just going to run the transactions serially)

    python3 conndemo.py postgres

This took ~5-6 seconds for 1000 transactions, or about 180 tps. That's not the whole story, though; each transaction does 5 updates to the table part. (Also some updates to invitem.) This works out to somewhere in the ballpark of 1000 tps.

Experiment 2: lots of parallelism

Try with (NUMTHREADS, PERTHREAD) = (25,40), and also and also SORT=False and REQLOCKS=False. We get lots of deadlock messages, due do transactions waiting for one another. The number of invitem entries will be less than 10,000, due to overlap. The conndemo.py program prints at the end the total parts quantity; this should equal the change in select sum(quantity) from invitem;. Here is one typical deadlock message (MAXPART = 500):

transaction failed: ERROR:  deadlock detected
DETAIL:  Process 707 waits for ShareLock on transaction 211312; blocked by process 703.
Process 703 waits for ShareLock on transaction 211310; blocked by process 707.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (26,148) in relation "part"

Try with (2,500): still some deadlock

This is very dramatic with NUMTHREADS = 4 and with MAXPART = 50. In 2024, I got 171 seconds, almost three minutes (the in-class demo had TOTALTRANS reduced from 1000 to 200 so it would run in a reasonable amount of time. Surprisingly, with NUMTHREADS = 2 rather than 4 there can be very little contention.)

Experiment 3: Requesting locks

Let's try asking for the locks upfront, with REQLOCKS=True. Postgres lets us ask for a lock on a specific record (with partnum = %s, where %s here serves as a variable) with

    select partnum from part where partnum = %s for update;

According to the Postgres manual, "FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends."

We can, by the way, lock an entire table (eg part) with the following (though we do not)

• lock part in share row exclusive mode
• lock part in exclusive mode
• lock part in access exclusive mode

If NOWAIT is false, then  each transaction waits for each lock in turn. If a lock fails, the request deadlocks, and we start the lock request over.

To summarize the values:

• MAXPART = 500
• NUMTHREADS=25
• PERTHREAD=40
• REQLOCKS=True
• NOWAIT=False
• SORT=False

This works, but often takes 20-40 sec!

In this scenario, when a transaction can't get its locks, it aborts (with a deadlock!) and tries again immediately. But for each lock, it waits to see if it will be granted, or if the existing locks will lead to a deadlock. Should we wait between tries a little bit? If so, how long? Should it put the transaction back to the end of that thread's queue? We don't really have a queue. (Note that the lock requests may deadlock, but then we just handle the exception, silently, and try again.)

Try enabling that print statement in the first exception handler

What happens when we reduce RETRYCOUNT? We can go to 5, and it still usually works. The Python script runs almost silently, with almost no transaction failures. But my 2024 time was ~120 seconds.

If we set NOWAIT=True, we get a handful of lock failures (look for the "lockfails" count in each "Exiting thread"  report), and a failure of the total parts quantity to be reflected in the database. The total elapsed time, however, is down to 4-5 seconds again.

In this scenario, we ask for the locks in rapid succession, but if any lock can't be granted immediately, we release all locks and immediately try again.

We now need a much higher value of RETRYCOUNT; at least 20. Should we make it really big?

But look what happens to the total time! Much better! ~100 tps.

Changing to REPEATABLE READ does not make much difference.

Experiment 4: Sorting the updates

Now let's enable sorting the part numbers in each transaction. No problems! Total time is 1.6 seconds. Why does this work? Now that we're requesting locks in increasing order of part number, we are deadlock-free.

• MAXPART = 500
• NUMTHREADS=25
• PERTHREAD=40
• REQLOCKS=True
• NOWAIT=False
• SORT=True
  

Try:

• with/without NOWAIT
• with/without REQLOCK
• ISOLATION='repeatable read'
• ISOLATION='serializable'

250 tps is not bad. But how does Amazon do it? How could you speed this up?

• partitioning the parts database
• entering sales in a separate table, and then updating the parts table at intervals
• accepting some potential non-atomicity

Experiment 4.5: try the above but with REPEATABLE READ.There are a lot more transaction failures, and a total time of about 3.2 seconds.

Experiment 5: table partupdate.

This has the following definition:

create table partupdate (
    invnum  integer,
    partnum integer,
    quan_sold integer,
    constraint partupdate_invnum  foreign key (invnum) references invoice(invnum),
    constraint partupdate_partnum foreign key (partnum) references part(partnum)
);

Instead of updating the quan_in_stock entry of table part, we create in table partupdate a record (invnum, partnum, quansold). We will then update part.quan_in_stock from this partupdate record at some later date, as part of a "batch" update to table part.

This flirts with atomicity failure, in that part.quan_in_stock may not be accurate. However, if the partupdate record is safely written, we shouldn't risk loss of updates.

The point of all this is that appending records to partupdate should not require locks. Because we include invnum, two different transactions should never be trying to insert the same record.

But we're still at around 4000 ms for 1000 transactions (250 tps).

My file here is conndemo3.py.

Every so often we need to run the following transaction to migrate all the partupdate data into part: 

begin;
with sums as
(select p.partnum, sum(p.quan_sold) as numsold from partupdate p where p.partnum = PARTNUM group by p.partnum)  -- summarize partupdate data
update part p set quan_in_stock = quan_in_stock - (select s.numsold from sums s where s.partnum = p.partnum) from sums where p.partnum = sums.partnum;

delete from partupdate;
commit;

Note that we do not need a "foreach partnum that has seen some sales, update part ...." loop.

Experiment 6: table locks

If each transaction locks the entire parts table with

    lock part in exclusive mode;

then the 1000 transactions (25 threads) takes 7-8 seconds, for about 125-135 transactions per second. Exclusive table locks, in other words, do slow things down, versus the best version we could do with row locks. The file is cdtablelock.py.