Are databases single-threaded?

Question

Let's say there is a hospital that runs a simple database. In this database is a table called "patients" with 1,000,000 records--each record being a patient whose status is either "active" or "discharged".

Bob runs the following query that will take a few seconds or maybe minutes to run:

SELECT COUNT(*) FROM PATIENTS WHERE STATUS = "active"

At the moment Bob begins execution of the query, 100 patients are active.

However, while the query is running, Susie runs the following command on the table:

UPDATE PATIENTS SET STATUS = "discharged" WHERE PATIENT_ID = 583739789

This command discharges one of the active patients, changing the real "active" patient count to 99.

After Susie does this, Bob's query finishes. Will the result of his query be 100 or 99 active patients? How do the different databases handle this (Oracle, MySQL, SQLite, etc)?

Judy

Depends on the database, How it is coded. For any commercial database, the capacity for *concurrency" is built-in, but is described by the concept of "transactions", which is a complex, and multi-faceted topic, with many scenarios, and allows for varying degrees of logical and physical separation between threads of execution. In general, for any commercial database, the way they handle the various scenarios is very, very similar if not identical. – Hypnos

This will be helpful. – Regardful

Answer 1

Given the question mentions SQL queries i'm going to assume you mean "are all SQL queries single threaded".

SQL servers are designed to handle multiple connections but every entry in to the transaction log has to be handled sequentially.

In short, many people can on multiple threads be connected to the server, but only 1 transaction can occur at any given point in time.

The better way to look at this is a bit like accessing a file (databases are much the same in this respect).

2 threads can't really write "reliably" to a file at the same time without some specific knows being in place (e.g. what part of the file is being written), but even knowing that would result in the thread writing to the earlier part of the file pushing content around later in the file.

So for this kind of reason SQL is "transactional" and handles each statement in a sequential manner ... it's just really good at doing it fast!

Nuncle

"2 threads can't really write reliably to a file at the same time without some specific knows being in place". In my example only one of the threads is writing to the table (Susie's query). So, it isn't a case of two UPDATE queries at the same time, but rather a SELECT, then UPDATE. Does the database still put a lock on the table when a SELECT query is running? – Judy