Help Needed in Transactions

Hello Friends,

I have a scenario as mentioned below,

TableA,TableB,TableC

I am taking records by joining the TableA and TableB then inserting into TableC. I dealing this with one million of data and it is inside the transaction. during this operation i don't want to lock the TableA and TableB. Also, TableA and TableB will not gets updated during this time and even if it's get updated i don't care. My only concern is during the transaction A,B table should not be locked as it will be used by my website for displaying data. How do i achieve this? Any sample please

August 29th, 2015 10:32pm

If all you are doing to TableA and TableB is reading them, then other processes will also be able to read those tables.  It is possible to prevent other processes from reading those tables by specifying an XLOCK query hint, but the default behavior is reads do not block other reads.

Tom

Free Windows Admin Tool Kit Click here and download it now
August 29th, 2015 10:50pm

Hi Tom,

Thanks for the reply and if my understanding is correct about your answer that  TableA and TableB willnot get locked though it is used in transaction.

I don't want to lock the tables. For example, i have created procedure which has the transnational operation as mentioned in my original post and scheduled in a job. please assume that it's dealing with million of data and once the sql job triggers that procedure  and if the procedure take 10 min to process the data because if huge data transformation, during this time, transaction will not get committed unless the process gets over. During this time those two tables should not get locked. Those two tables should be available for other resources to use it.

How to achieve this?

August 29th, 2015 11:17pm

The Idea of using transaction seems little daunting, what if just before the last insert is finished the insert fails you would have to wait for whole data to rollback and believe me this is really a nightmare keeping in mind that you don't want tables to get locked. Is transaction really required why can't we just do insert without transaction

To your other question I would suggest to use Optimistic Isolation level either Read committed snapshot (RCSI) or snapshot Isolation level but I would go for RCSI.

Free Windows Admin Tool Kit Click here and download it now
August 30th, 2015 2:02am

Yes, if what you are doing is something like

Begin Tran
Insert TableC (<column list>)
Select <column list>
From TableA
Inner Join Table On <On Condition>
Where <Where Condition>

That will not prevent other connections from selecting, inserting, deleting or updating rows in TableA or TableB.

Tom

August 30th, 2015 2:22am

If all you are doing to TableA and TableB is reading them, then other processes will also be able to read those tables.  It is possible to prevent other processes from reading those tables by specifying an XLOCK query hint, but the default behavior is reads do not block other reads.

Tom

Free Windows Admin Tool Kit Click here and download it now
August 30th, 2015 2:47am

Thank Shanky and i have an answer for your questions. I am dealing  with 5 steps in the logic. The sample provided by Tom is 3 rd step and i am logging each step in the separate  table to track each step done successfully. As per your statement if insert gets fails at the end of the transaction, the table A and table B  gets locked till it gets rollbacks.

Hi Tom,

thanks for your reply and lets assume the Shanky's situation that if something gets failed at the end of insert what would happen?

August 30th, 2015 9:56am

It depends on what type of recovery happens.

In a normal recovery, such as when your application program does a rollback or the application program is terminated for some reason without doing a commit or rollback (that automatically causes a rollback), then you will not lock TableA or TableB during the recovery.   All a rollback recovery does is read the database log, and apply before images to updated rows.  So the rollback does not depend in any way upon TableA or TableB and will get no locks on those tables.

But if the whole database instance fails, for example, the database server is rebooted (note this is the server your database is on, not the box you are running your INSERT on), then when the server comes back up, the entire database will be locked out until the recovery completes.  So in that case, TableA and TableB (and everything else in your database) will be unavailable until the recovery completes.  Note that this applies whether or not you are in a transaction state.  If you were not in transaction state and were running an INSERT (or UPDATE or DELETE) that took 10 minutes to run, and the server was rebooted 9 minutes into that update, the recovery of that database would likely take several minutes (often as long as the original update had been running) and during that time your entire database would be unavailable.

For that reason, (and other problems, like your log getting large), long running updates and transactions are often best avoided in systems which have high availability requirements.  If you must do them, try to do them during times when other load and uptime requirements are minimal (nighttime, weekends, etc).

Tom

Free Windows Admin Tool Kit Click here and download it now
August 30th, 2015 6:49pm

Hi Tom,

thanks and lets assume that i have procedure and the sample query that we discussed on above post is inside the procedure. then i created a job that runs the procedure and this will happen once a day throughout the year.so logically there is not client server communication here. because job directly runs in the server. On this occasion will those two table lock?

August 30th, 2015 8:15pm

Short answer: no.  Long answer:  The answer to almost every computer question is "it depends".  Best answer:  Create a test system with real world amounts of data and transactions.  Then run tests to see what actually happens.  Practical answer:  Locking of TableA and TableB in the scenario you describe will probably never be a problem and it will almost certainly never be the biggest problem you will have, and therefore, is not worth worrying about.

Tom

Free Windows Admin Tool Kit Click here and download it now
August 30th, 2015 10:29pm

Thank Shanky and i have an answer for your questions. I am dealing  with 5 steps in the logic. The sample provided by Tom is 3 rd step and i am logging each step in the separate  table to track each step done successfully. As per your statement if insert gets fails at the end of the transaction, the table A and table B  gets locked till it gets rollbacks.

I am not talking about whole database recovery I was just focusing on what if your job fails and you should focus on that only. No table A and table B would not get locked for period of rollback, it might frequently get locked and thus creating blockings.  Normally in inserts explicit transaction is avoided as if it fails insert can be done again and if you have timestamp column it is even more better you can compare against timestamps and can insert based on time.

thanks and lets assume that i have procedure and the sample query that we discussed on above post is inside the procedure. then i created a job that runs the procedure and this will happen once a day throughout the year.so logically there is not client server communication here. because job directly runs in the server. On this occasion will those two table lock?

Lets first understand that there is difference in Block and Lock. No matter what some lock would always be taken because on this principal RDBMS is based on other hand excessive blocking is what you should be worried about. There would always be some kind of Client server architecture even if you run job a request would be sent to server to process it. So now are you worried about locks or blocking ?



August 31st, 2015 12:47am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics