.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Easy Web
Imran Ghani
Post New Web Links

How to lock a table during the whole transaction?

Posted By:      Posted Date: September 20, 2010    Points: 0   Category :Sql Server
 

In my procedure, I need first to run a merge table T using table S and then truncate table S. And other session might insert data into the table S during my execution of the procedure. So I'd like to lock the table in exclusive mode for the whole transaction, but how to do that?

 

If I only lock the table during the merge phase, if after merge completed but before truncate begin, another session inserted some data into it, then the data in S will be missing when run the procedure again.

 

begin

  merge T using S .....

 truncate table S;

end

 

 




View Complete Post


More Related Resource Links

SSIS Package Transaction locks entire table

  
Hello, I have SSIS package that using transactions and working as expected. But this locks all the tables involved in the data flow that are get inserted/loaded. If I query those tables during package execution the query waits till SSIS releases the lock. But I can still query those tables using NOLOCK hint. Is this possible SSIS will lock only the rows that it insert/update/delete etc? So that other user can still use that table or another instance of same SSIS Package can load data into those tables? My Package: In the package level I have TransactionOption = Required. The Sequence (Data Flow is inside this sequence) TransactionOption = Supported. I had some issue in my package that I posted here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/dfb5a8b9-d43c-452b-bb21-d6aea192fc97 and also resolved. Now I descovered this issue and trying to resolve. Thanks, Prabhat

How to lock tables during a transaction

  
Hi, is there a way do something like this:  BEGIN TRANSACTION --lock table1 --do work here --unlock table1 COMMIT TRANSACTION

Proposal transaction table

  

Hi All,

I'd like to know how to save the amount of transactions?

CheckBox CheckBox1 = LoginView2.FindControl("CheckBox1") as CheckBox;
        RadioButton RadioButton1 = LoginView2.FindControl("RadioButton1") as RadioButton;

        if (CheckBox1.Checked == true)
        {


            if (RadioButton1.Checked == true)
            {

                RequiredFieldValidator RequiredFieldValidatorCVC22 = LoginView2.FindControl("RequiredFieldValidatorCVC22") as RequiredFieldValidator;
                RegularExpressionValidator RegularExpressionValidatorCreditCardNumber = LoginView2.FindControl("RegularExpressionValidatorCreditCardNumber") as RegularExpressionValidator;
                RegularExpressionValidator RegularExpressionValidatorNameOnCard = LoginView2.FindControl("RegularExpressionValidatorNameOnCard") as RegularExpressionValidator;
                RegularExpressionValidator RegularExpressionValidatorCVC2 = LoginView2.FindControl("RegularExpressionValidatorCVC2") as RegularExpressionValidator;
                RequiredFieldValidator RequiredFieldValidatorCVC2 = LoginView2.FindControl("RequiredFieldValidatorCVC2") as RequiredFieldValidator;


                if (RegularExpressionValidatorCreditCardNu

Why "sa" account is trying to obtain lock on user table?

  

Hi,

I'm monitoring Lock:Timeout value on SQL database for specific table and specific type of lock (lock number 8). I'm seeing a lot of lock acquired/released as well as Lock:Timeout from "sa" account and listed as IsSystem=1.

Why is it happening. How do I catch in profiler specific details related to those locks?

 

Thanks,

Greg

 

P.S. This is on SQL 2008 x64 SP1.


Log to a table from within a stored procedure with transaction

  

I want to do the following:

Execute a stored procedure that starts a transaction, and from within that stored procedure call another stored procedure that is outside transaction.

The easiest to explain this is with a logging example (my situation is slightly different but it's the same principle)

So, from within a transaction writing a log to another table. However, when my procedure fails, all logging is gone because the transaction is rolled back.

The following example is an example (don't shoot me if it's not valid tsql)

CREATE PROCEDURE usp_X
AS

BEGIN TRANSACTION
INSERT INTO TABLE1 VALUES (NEWID(), 'abc')
exec usp_LogEvent('record created'
COMMIT TRANSACTION


CREATE Procedure usp_LogEvent
varchar(1

how to select the last transaction Detail in table

  

Hi all,

i am working on the migrating project now i want select list of records depending on the last date of transaction and i have to fetch one record for each account please help me out 

i have data as below

ac_no                trdate              balance ---------------

1                     2010-3-31           100     -------

1                     2010-4-1              50         ----------

1                     2010-4-5             500     ----------

2                     2010-3-31           200

2  &nbs

getting ERP transaction timestamp translated to fiscal period and year (the lookup table exists)

  

Not sure if this is correct forum as it's SQL syntax specific.

Putting in new ERP system which does not hold fical period/year stamps in the transaction records, just the datetime timestamp.  There is a GL table which holds the period year and the begin and end dates.

Need to find a code snippet that I could hook into any sql statement so that I could pull the period and year from the GL table. 

Not sure how to code in sql if the timestamp of the txn is less than x but greater than y, give me the corresponding gl period and year.

Any pointers/links would be great. 

Thanks,

Robert

add:  this is sql server 2008, if it matters.


getting ERP transaction timestamp translated to fiscal period and year (the lookup table exists)

  

Not sure if this is correct forum as it's SQL syntax specific.

Putting in new ERP system which does not hold fical period/year stamps in the transaction records, just the datetime timestamp.  There is a GL table which holds the period year and the begin and end dates.

Need to find a code snippet that I could hook into any sql statement so that I could pull the period and year from the GL table. 

Not sure how to code in sql if the timestamp of the txn is less than x but greater than y, give me the corresponding gl period and year.

Any pointers/links would be great. 

Thanks,

Robert

add:  this is sql server 2008, if it matters.


Deadlock: System.Data.SqlClient.SqlException: Transaction (Process ID 51) was deadlocked on lock res

  

 Hello,

 

I have a deadlock problem that occurs every 5 minutes on SQL update. I get the following error randomly - means qurey failed one time every 200 calls. I'm using transaction scope to manage transaction.

 

 

code:

using(TransactionScope scope = new TransactionScope())

{

     SELECT ... FROM TABLE1

 

     UPDATE TABLE1 SET [F1] = @F1, [F2] = @F2, [F3] = @F3, [F4] = @F4, [F5] = @F5 WHERE UId = @UId

 

 

      scope.Complete();

}

 

 

error:

System.Data.SqlClient.SqlException: Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

 

stack tr

Why following query trying to get Sch-M lock on a table?

  

Hello,

Why would query below caught into dynamic view causing Sch-M type lock on a table. My understand Sch-M lock are only if you plan to modify schema on table which is not a case here.

 

 

<t1 resource_type="OBJECT" database="DB" blk_x0020_object="1363535941" request_mode="Sch-S" request_session_id="67">

<t2 blocking_session_id="65" wait_duration_ms="30386" query_text="INSERT INTO @tmpTableTrips

SELECT TOP 50  

t1.idCont,

t1.idParent,

t1.tripPassFName AS contFName ,

t1.tripPassLName AS contLName,

t1.contCompany AS contCompany,

ISNULL(a1.contAcctNumber, '') AS contAcctNumber,

ISNULL(t1.contAcctNumber, '') AS contBCAcctNumber,

t1.tripPassEmail AS contEmail,

t1.tripPayType AS contPaymentMethod,

t1.contName AS contName,


Transaction (Process Id 106) was deadlocked on lock

  

Dear All,

 

I am getting this error continously please help me out

 

 Transaction (Process ID 106) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.InternalEx

Export Visio Database Table Names to Excel

  
If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Insert value using Table Value Functions

  
a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

Using jQuery to Filter Table Rows

  
The project is using the .net GridView control, so I had limited control over the output HTML code. Still, I think this code can work for most tables. One thing to notice: you should use the class "filterable" on your table or on one of its parents for the code to work.
First, we need a text box:

RadioButton Groups, Table Rows And NamingContainers in asp.net

  
The RadioButtonList. Very handy for inviting users to select just one item from a list although because of the screen real estate it takes up, used less and less in favour of the DropDownList. In plain HTML, the browser knows to enforce the unique selected value amongst a list of radio buttons because they all have the same value for their name attributes.

RadioButton Groups, Table Rows And NamingContainers

  
(In which AJAX-enhanced CheckBoxes become more useful than RadioButtons but inheritance saves the day, and a simple RadioButton-derived control establishes the purpose of a control's naming container)

The RadioButtonList. Very handy for inviting users to select just one item from a list although because of the screen real estate it takes up, used less and less in favour of the DropDownList. In plain HTML, the browser knows to enforce the unique selected value amongst a list of radio buttons because they all have the same value for their name attributes.

Building a 3-Tier App with Silverlight 3, .NET RIA Services, and Azure Table Storage

  
Silverlight 3, .NET RIA Services, and Windows Azure Services Platform makes a 3-tier Cloud application easier to build: Silverlight 3 as presentation tier, .NET RIA services as the business logic and data access tier, and Windows Azure Table as the data storage tier. The sample application in this article demonstrates the architecture with a simple Survey application with all these technologies working together from Windows Azure.
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend