Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
stored procedures
- Thread starter Nancy
- Start date
Hi Nancy,<br>
<br>
We should be able to get you going - can you give a few more detail?<br>
<br>
Database<br>
<br>
ODBC - or whatever (Oracle Objects for OLE for instance)<br>
<br>
Sample bit of code that's not working<br>
<br>
The error you're getting<br>
<br>
Regards<br>
<br>
Mike<br>
<br>
We should be able to get you going - can you give a few more detail?<br>
<br>
Database<br>
<br>
ODBC - or whatever (Oracle Objects for OLE for instance)<br>
<br>
Sample bit of code that's not working<br>
<br>
The error you're getting<br>
<br>
Regards<br>
<br>
Mike<br>
- Thread starter
- #3
I am inserting records into a table on the SQL server and calling a stored procedure in SQL from Visual Basic using an RDO connection.<br>
<br>
This is an example of the code:<br>
<br>
begin transaction<br>
<br>
insert spcl (agency, Client, Time_Stamp, Record_id_Number)<br>
<br>
values (@agency_code, @Client_Number,@Time_Stamp, @record_id_Number)<br>
<br>
commit<br>
<br>
Occasionally when this is called, the SQL server locks. And, I can not understand why. It is a simple stored procedure. I would also like the record to be updated instead of inserted if it is already out there, but I want to keep it as simple as possible until I am sure it is working properly.<br>
<br>
I appreciate your help.
<br>
This is an example of the code:<br>
<br>
begin transaction<br>
<br>
insert spcl (agency, Client, Time_Stamp, Record_id_Number)<br>
<br>
values (@agency_code, @Client_Number,@Time_Stamp, @record_id_Number)<br>
<br>
commit<br>
<br>
Occasionally when this is called, the SQL server locks. And, I can not understand why. It is a simple stored procedure. I would also like the record to be updated instead of inserted if it is already out there, but I want to keep it as simple as possible until I am sure it is working properly.<br>
<br>
I appreciate your help.
Hi Nancy,<br>
<br>
Ok. You probably know all this stuff but bear with me anyway.<br>
<br>
You say "Occasionally" so I guess this works mostly? Whenever I get an update that mostly or sometimes works but sometimes locks the DB that's exactly what I think of .. <br>
<br>
LOCKS! <smile><br>
<br>
As you're not getting an error message the DB might just be waiting for a resource that's currently busy - that would be my first check. Is that table locked because there's another insert going on? (from a previous session of yours?)<br>
<br>
Next thing would be the patch level of the SQL Server DB. Are you running with the correct service packs etc?<br>
<br>
Does SQL Server have error logs? (it must do I guess) Have a look through those.<br>
<br>
And the other obvious thing: Is the data you're trying to insert valid? Does that statement work in an interactive SQL session?<br>
<br>
<br>
Regards<br>
<br>
Mike<br>
<br>
<br>
Ok. You probably know all this stuff but bear with me anyway.<br>
<br>
You say "Occasionally" so I guess this works mostly? Whenever I get an update that mostly or sometimes works but sometimes locks the DB that's exactly what I think of .. <br>
<br>
LOCKS! <smile><br>
<br>
As you're not getting an error message the DB might just be waiting for a resource that's currently busy - that would be my first check. Is that table locked because there's another insert going on? (from a previous session of yours?)<br>
<br>
Next thing would be the patch level of the SQL Server DB. Are you running with the correct service packs etc?<br>
<br>
Does SQL Server have error logs? (it must do I guess) Have a look through those.<br>
<br>
And the other obvious thing: Is the data you're trying to insert valid? Does that statement work in an interactive SQL session?<br>
<br>
<br>
Regards<br>
<br>
Mike<br>
<br>
- Thread starter
- #5
As far as I can tell by looking at the error log on the SQL Server, there is only one user accessing the table; however, that user gets an error message and all of the other users accessing the SQL Server (not that table) are kicked off.<br>
<br>
The data I am trying to insert is valid. And we are running with the correct service pack. <br>
<br>
The only thing I can think of is that ... when we run one of the programs, it creates a lot of page locks. And after a certain number of page locks, a table lock occurs. I don't want to increase the number of page locks that can occur before a table lock occurs because then I have problems with my temp db.<br>
<br>
I still don't understand why the entire server would freeze. And, I thought the SQL Server was a little more adept at managing locks so that no error would occur.
<br>
The data I am trying to insert is valid. And we are running with the correct service pack. <br>
<br>
The only thing I can think of is that ... when we run one of the programs, it creates a lot of page locks. And after a certain number of page locks, a table lock occurs. I don't want to increase the number of page locks that can occur before a table lock occurs because then I have problems with my temp db.<br>
<br>
I still don't understand why the entire server would freeze. And, I thought the SQL Server was a little more adept at managing locks so that no error would occur.
Sounds reasonable - that your page locks would eventually be promoted to a table lock; but this is a normal event and shouldn't cause it to freeze - let alone give an error message. What's the error message that user (who's doing the insert) gets?<br>
<br>
-ml<br>
<br>
-ml<br>
- Thread starter
- #7
And then it kicks *everybody* off? God bless Microsoft - they really know how to program in Seattle don't they?<br>
<br>
1) It might be a bug. SQL Server might think there's a deadlock when there isn't (something is certainly handling the condition inelegantly)<br>
<br>
2) There *might* be something else after that table. I know - I know - you're certain there's not.<br>
<br>
A deadlock is where (I'm sure you know this but bear with me) your process gets a lock on A and attempts to lock B. B is already locked by somebody else (so you can't lock it) who then tries to lock A (which you've locked so that fails). This then goes 'round in a nice little loop because neither process thinks to unlock everything and try the whole thing again.<br>
<br>
Two things to try:<br>
<br>
a) Try this again when no-one else is using the database. If it fails then, hmmmm.<br>
<br>
b) Have a look at what else your process is doing - as far as I'm aware deadlocks only occur when you're trying to lock more than one object (that might be more than one row in the same table - not just more than one table)<br>
<br>
Go for it Nancy.<br>
<br>
Mike<br>
<br>
1) It might be a bug. SQL Server might think there's a deadlock when there isn't (something is certainly handling the condition inelegantly)<br>
<br>
2) There *might* be something else after that table. I know - I know - you're certain there's not.<br>
<br>
A deadlock is where (I'm sure you know this but bear with me) your process gets a lock on A and attempts to lock B. B is already locked by somebody else (so you can't lock it) who then tries to lock A (which you've locked so that fails). This then goes 'round in a nice little loop because neither process thinks to unlock everything and try the whole thing again.<br>
<br>
Two things to try:<br>
<br>
a) Try this again when no-one else is using the database. If it fails then, hmmmm.<br>
<br>
b) Have a look at what else your process is doing - as far as I'm aware deadlocks only occur when you're trying to lock more than one object (that might be more than one row in the same table - not just more than one table)<br>
<br>
Go for it Nancy.<br>
<br>
Mike<br>
Hi MikeLacey<br><br>Just have sometimes fulling around this forum to find some tips, and I found this one.<br><br>I just have a question about the message: <br><br>"Your server command (process id 19) was deadlocked with another process and has been chosen as deadlock victim."<br><br>Yes, longtime ago, I get this message, but because I don't know Sybase much and I don't work on the db often, I told my coworker what happens, after that she informs me she rerounds the process. I ask her how she does it, but she refuses to show me. Now, I involve with the db and Sybase (but still don't know much about it <g>!!) and I sometimes see the similar msg again, I would like to know what my coworker said "reround/redirect the process", and how does she do it? so that I can do it by myself rather calling her help.<br><br>Thanks.<br>
Hi Nancy,<br><br>When you get a deadlock SS arbitrarily select a process to kick off.<br><br>If you are sure that no other process is locking that table, I can only think of one other reason. In SS theres a db_option (in v6.5) called "insert row level locking" - this gives you row level locking on INSERTs only (SS uses page level locking normally). <br><br>If your process inserts a row(s) and in the same sp trys then to update those rows, you can deadlock yourself as ther insert is waiting to lock the page but it already has a row level lock.... I have seen this once b4 (we spent ages trying to find the cause)<br><br>I no its convoluted but worth a try<br><br> <p>Cal<br><a href=mailto: > </a><br><a href= > </a><br>
Hi Nancy, you've got us all curious with this one!<br><br>Another thought, are there any triggers on this table that may be running off and doing all kinds of things every time you insert a record, perhaps coming back to the spcl table?<br>
- Status
