|
You need to read up on transactions. Don't think in terms of locking, think in terms of what operations are permitted to occur concurrently.
The easiest, but least performant, method is to use the Serializable transaction isolation level. This prevents all updates to data you've read or updated since the beginning of a transaction until you commit the transaction. SQL Server simply blocks any client trying to make a modification, or read one of your modifications, until your transaction is committed (if using locking, rather than row versioning). There are lower isolation levels that you should read up on carefully before using to ensure that the effect is the desired one. The default level is ReadCommitted, which means that you cannot read uncommitted updates which another transaction has made (and if SQL Server is using locking rather than row versioning, blocks your connection until the changes are either committed or rolled back).
You should explicitly wrap your operations that need to have a consistent state in a BEGIN TRANSACTION/COMMIT TRANSACTION pair of statements, or use a SqlTransaction object. If an error is returned from a data-modification operation, you should roll back the transaction and abandon any further statements. At any point if you decide you need to discard your operations, you can roll back (note that everything you did in the transaction is rolled back).
Be aware that a second connection opened by your application is treated as a separate client and you can end up blocking yourself. This does not apply to SQL Server 2005 with the Multiple Active Result Sets (MARS) feature enabled.
Also be aware that if you perform operations in a different order in one transaction compared to another, you can end up with a deadlock. In this case, SQL Server will decide to kill off one of the transactions so that another complete. The transaction that is killed will be rolled back.
Sometimes you need to lock a record on a logical level for updates and still permit other users or clients to read it. Here you will have to roll your own - SQL Server does not provide support for this.
It's important for all database developers to understand transactions and isolation if the database is going to be used concurrently, as the vast majority of server-type databases will be.
|
|
|
|
|
Hi all,
I want to creat a report that displays the accNo and its subsidiary accounts how can i write an SQL Sever query to get both of the main account and its sub account in one query?
Thanx in advance
|
|
|
|
|
can please explain explain your table strecture, sample data and required output out of it
Regards
KP
|
|
|
|
|
Hi
This is a sample structure close to the structure i have
ID Name Parent
1 ddd NULL
2 sss 1
3 aaa 1
4 fff 3
i want to be able to select (1) and its children (2,3)
output like this
1 ddd
2 sss
3 aaa
3 aaa
4 fff
Thanx
|
|
|
|
|
create table a(id int, nam varchar(5), parent int)
insert into a values(1, 'ddd', null)
insert into a values(2, 'sss', 1)
insert into a values(3, 'aaa', 1)
insert into a values(4, 'fff', 3)
Query for fetching data ...
select id, nam
from (
select distinct x.* from a x inner join a y on x.id = y.parent
union all
select distinct y.* from a y inner join a x on x.id = y.parent
)b
order by id
and the output is ...
id nam
---- ------
1 ddd
2 sss
3 aaa
3 aaa
4 fff
Regards
KP
|
|
|
|
|
Thanx alooooooot
it worked
|
|
|
|
|
Thankx for confirmation
Regards
KP
|
|
|
|
|
to fetch data from one table relating to itself need ot self join as shown in my example.
where i'm fetching from same table with alias as X & Y and joining X & Y.
Regards
KP
|
|
|
|
|
Hi all
I have one mail application in which i was using Java and ms access as back end but now i want to connect my Java application with SQL server , can any one suggest me how to that like what are the settings i need to change
The code what i was using is like this
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String str1="jdbc:odbc:Users";
Connection con1=DriverManager.getConnection(str1);
Statement st1=con1.createStatement();
ResultSet rs=st1.executeQuery("SELECT Userid FROM Userinfo");
PL help me
Regards
Prakash Mishra(Banglore,India)
|
|
|
|
|
Go to control panel --> administrative tools --> data sources.
Create a new data source but connect it to a SQL DB(I think it's at the bottom) instead of your access DB. Give the connection a name and replace jdbc:odbc:Users with jdbc:odbc:NEWCONNECTIONNAME. I haven't used Java for a while now but I'm pretty sure that will work. 
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
I want to lock a table in SQL Server, within a transaction. Unless i release lock, no other transaction should not be able to even read (execute select statement) on that table.
Any Ideas?
kumar
|
|
|
|
|
Hi Kumar
You should be able to do the following within a transaction:
SELECT @dummy = COUNT(*) FROM MyTable WITH (TABLOCKX, HOLDLOCK)
The "TABLOCK" and "HOLDLOCK" keywords are locking hints. You should be able to look these up in your Books-Online.
What are you trying to do while the table is locked? I would advise you to keep your transaction reeeeaaaally short.
Regards
Andy
|
|
|
|
|
thanks Andy,
Actually this is just for testing purpose, i want other transaction to wait till the current transaction releases the lock. Is there any mechanism, where in we can hold the lock on table for few seconds so that other transaction should be able to atleast execute Select command?
|
|
|
|
|
sorry, i am correcting my statement above, "so that other transaction should not be able to atleast execute Select statement"
|
|
|
|
|
I normally test these things by opening two sessions in Query Analyzer. However if you want to test this using a stored procedure then use the "WaitFor" T-SQL command to wait for a period of time.
|
|
|
|
|
hi,
i want to use TABLOCKX lock on my table. i have used same query given in above post.
but problem is lock does not get released after completion of transaction holding lock on table. hence another transaction keeps waiting.
following are my 2 query i tried in query analyzer of sql server.
Please help me...
Query 1:
BEGIN TRAN A
SELECT * FROM alerts with (TABLOCKX, HOLDLOCK)
declare @cnt bigint
set @cnt = 1000000
print 'start time = '+cast(getdate() as varchar)
while @cnt > 0
begin
print 'Cnt = ' + cast(@cnt as varchar)
set @cnt = @cnt - 1
end
print 'end time = '+cast(getdate() as varchar)
COMMIT TRAN A
-----------------------------------------------------------------------------------
Query 2:
BEGIN TRAN B
print 'start time = '+cast(getdate() as varchar)
SELECT * FROM alerts --with (TABLOCKX)
print 'end time = '+cast(getdate() as varchar)
COMMIT TRAN B
Please let me know if i'm doing anything wrong.
Thank in advance...
Rajesh.
|
|
|
|
|
you can create trigger for dml staements such as select,update ,delete..So create a trigger while selecting the table.
PPK
|
|
|
|
|
Hi,
I work on a project which is connected to mysql.
But when I connect to mysql I connect with "localhost",
bu I can't connect with my IP.
Please help.
Thanks.
|
|
|
|
|
Can you connect using 127.0.0.1?
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
Hi,
Thanks for your reply but I can't connect with 127.0.0.1.
|
|
|
|
|
Hi, I don't really know MySQL but try:
Turning off any firewalls that might be running.
Making sure the service is started properly on your computer.
If thats not the problem perhaps try reinstalling MySQL.
There are 10 types of people in the world, those who understand binary and those who dont.
|
|
|
|
|
how to programmatically add the values of a apecific field in a .mdb file.(only that field is blank, the other fields are filled)
thanks
|
|
|
|
|
Send the appropriate UPDATE SQL Command to the database.
|
|
|
|
|
Between Stored procedure and user defined function which is faster and why?
|
|
|
|
|
They solve different problems. A speed comparison is invalid.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
