Sql-server – Transaction Isolation level different threads massive number of Writes

isolation-leveljdbcsql servertransaction

I have a lot of threads that writing to DB some data in two tables. tbl_raw_data and tbl_parsed_data where tbl_parsed_data have foreign key to tbl_raw_data.

I also need the writing to be superfast.

While checking the options on improving the writes (asuming reading time is nu so important) a friend of mine told me that I need to check the Transaction Isolation Level that is appropriate for my logic.
after reading some articles regarding this issue what I understand is that this property influence reading.

Is there a Transaction Isolation Level that affect writing? Which isolation level is "best" for lots of Threads running on lots of connections?

Am I looking in the wrong place?

What can I do to improove massive writing?

Im using SQL server, the threads are coming from TomEE server that writes data that came via HTTP requests via JDBC and JPA (ORM).

Thanks!

Best Answer

An ORM requires information about the first write (SCOPE_IDENTITY or such) to complete the second write. This means 2 (with an OUTPUT clause) or 3 database (with SELECT SCOPE_IDENTITY) calls in general in a client side transaction.

No amount of tinkering with isolation levels will eliminate these 2 or 3 calls.

If you want more performance, then the best concept stored procedure to do an atomic write (in a transaction) to both tables in one database call. This means 50% or 66% reduction in database calls

Going further, you have to decide what "superfast" really means

For example, should you be using SSDs for your transaction log files: the drive hosting the log files determines overall write speed because of write ahead logging.

Then there the design: are you using IDENTITY columns or GUIDs or Hibernate style nvarchar keys? Has the ORM designed the database for you?

Related Solutions

Sql-server – Does the transaction isolation level affect indices as well

No. It doesn't affect anything regarding the internal management within SQL Server. You're setting that connection for you, for your queries. SQL Server manages it's own locking it's own way.

Why would you turn off page and row locking on an index? You're more likely to see more severe locking than if you let SQL Server manage that index as it sees fit. By setting both those values to OFF, you just told SQL Server to take a table lock out. Considering you're also messing with read_uncommitted, I'll bet you don't want that.

If you're asking does read_uncommitted allow you to get dirty reads on indexes, yes. It does.

Postgresql – Read Committed Isolation Level

The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:

CREATE TABLE table_to_be_updated (
    id serial PRIMARY KEY,
    other_column text,
    column_changing text
);

INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');

Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):

                                | <-- BEGIN;
                                |
                                |
                                |     UPDATE table_to_be_updated
BEGIN; -----------------------> |     SET column_changing = 'new_value'
                                |     WHERE
                                |         other_column = 'value' AND
                                |         column_changing = 'old_value';
                                |
                                |
SELECT column_changing -------> |     -- update not yet committed
FROM table_to_be_updated        |
WHERE other_column = 'value';   | <-- COMMIT;
                                |
                                |
SELECT column_changing -------> |
FROM table_to_be_updated        |
WHERE other_column = 'value';   |
                                |
                                |
COMMIT; ----------------------> |

Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:

SET transaction ISOLATION LEVEL REPEATABLE READ;

(The command must be the first statement in a transaction.) Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.

Best Answer

Related Solutions

Sql-server – Does the transaction isolation level affect indices as well

Postgresql – Read Committed Isolation Level

Related Question