Having Transaction in All Queries

Does a transaction affect all queries?

Just your connection :-)

(Edit: rather your transaction, since the BEGIN TRANSACTION. If you did updates before the BEGIN TRANSACTION in the same session, they will of course not be rolled back)

BUT: It could have given SELECTs of other sessions the wrong answer depending on what lock types and query hints that were being used...

An example:

In one SQL Studio session, do the following:

CREATE TABLE a(a INT)

INSERT INTO a VALUES(1)

BEGIN TRANSACTION

UPDATE  a 
SET a = 2

SELECT *, @@TRANCOUNT
FROM a

-> You will see '2, 1' as result

Open a new session (tab in Sql studio)

Do:

SELECT *, @@TRANCOUNT
FROM a (NOLOCK)

You will see '2, 0'

Now, in first session, do

ROLLBACK TRANSACTION

SELECT *, @@TRANCOUNT
FROM a

-> transaction rolled back, and you see '1, 0'

-> a select in second session will also show '1, 0'

so: If you use (NOLOCK) hint, you can get uncommitted data as result -> which might lead to very unexpected effects :-)

Dont forget:

DROP TABLE a

when you're done ;)

SQL Query to find details of person having 3 transaction in a day

I believe in order to find the details of people who perform 3 transactions a day, you would need to change your query to include the date in the count as follows:

SELECT ANo,Tdate, count(*) as transcation_per_day 
FROM Deposit 
Group BY ANO,Tdate;

That will give you a table of the number of transactions per PID per day. You would simply then need to exclude anything less than 3 transactions, and then join it to the person table to get their details:

SELECT DISTINCT P.PID,P.PNAME
FROM PERSON as P
INNER JOIN
(
SELECT ANo,Tdate, count(*) as transcation_per_day 
FROM Deposit 
GROUP BY ANO,Tdate
HAVING transcation_per_day>=3
) as C
ON P.PID = C.ANO

Note: I added in distinct in order to remove duplicate PIDs in case you have a person who has 3 transactions over multiple days.

I am using the Transaction in sql to force all the query results in success.But It is not working

Basically, what you do is something like this:

BEGIN TRANSACTION;
BEGIN TRY

-- Multiple sql statements goes here

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH

Note that the commit transaction must be the last statement in the try block. This way, the transaction is only committed if no statement inside the try block raised an error, and is rolled back only if there was an error.

Should there be a Transaction for Read Queries?

So you get a consistent view of the database. Imagine you have two tables that link to each other, but for some reason you do 2 selects... in pseuodocode:

myRows = query(SELECT * FROM A)
moreRows = query(SELECT * FROM B WHERE a_id IN myRows[id])

If between the two queries, someone changes B to delete some rows, you're going to have a problem.

How to make SQL query to retrieve all last transactions for every year from a transaction table

Try breaking your TransactionDate into a year using DATEPART. Once you have that, you can select max TransactionDate and group by year, to get the last transaction of each year

That would look like this…

SELECT LastTransactionDateOfYear
FROM (
    SELECT DATEPART(year,TransactionDate),
        MAX(TransactionDate) LastTransactionDateOfYear
    FROM <yourtable>
    GROUP BY DATEPART(year,TransactionDate)
)

Can I use Postgres transactions only for write queries and use read queries without transaction?

So if you're going to query the database for the same rows that you just inserted using a transaction, but haven't committed the transaction yet, then you should read from the database using the transaction.
Eg. You create a user, then you need to create an external account for this user, and the method that creates that external account reads the user from the database and does not get it as a parameter. You can either modify the create external account method so it gets the user as a parameter and then pass to it the just created user, either you can keep the method like it is, but you have to make sure you pass the transaction to it. Otherwise, if the transaction is not committed and is not passed to the read query, the created user won't be found.
Ideally you should avoid this thing by passing the input data to the create external account too, so you don't need to read the user from db, but if for some reason this is not possible, then make sure you read from the db using the transaction.

When should I use transactions in my queries?

Basically any time you have a unit of work that is either sensitive to outside changes or needs the ability to rollback every change, if an error occurs or some other reason.

Look here for some excellent answers and their reasons for using them.

Does SQL write lock makes other transactions re-evaluate all queries in the tranasction

Let's put the steps in the order you describe:

Transaction 2:
B. variable b = SELECT count FROM seats WHERE user_id = 1

Suppose b now has value 10.

Transaction 1:
A: UPDATE table SET count = 20 WHERE user_id = 1

This locks the row until Transaction 1 finishes.

Transaction 2:
C. UPDATE seats SET count = {b} + 1 WHERE user_id = 1

This also wants the lock that Transaction 1 holds, so Transaction 2 must wait.

Transaction 1:
D: COMMIT

Transaction 1 releases its lock when it commits.

Transaction 2:
C. UPDATE seats SET count = {b} + 1 WHERE user_id = 1

It can now go ahead, because the lock it wants is free.

The row now has value 20, since Transaction 1 committed. But the client for Transaction 2 still has value 10 in its variable b, so it updates the row to 10 + 1, or 11.

This overwrites the value set by Transaction 1. A third transaction may query after Transaction 1 committed and before Transaction 2 commits. Then it will read the value 20. But it will soon be overwritten as soon as Transaction 2 commits.

---

Related Topics