Database Discussion Boards

Amr.Mohammad8725-Dec-15 7:22

25-Dec-15 7:22

Greetings,

I have a database creation script. I've run the script after changed the path within the 'FILENAME' option to create the database on an external hard disk and I was able to see and select data from tables as the script run with no errors. However, when I tried to connect to the database from an application and read data from database tables, an exception thrown for SQL Server error 823 "The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000132000 in file 'Database name'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.". I've tried to run database consistency commands against it but they did not carried out and I got an error message. Also when I tried to drop or recover the database, dropping and recovering process failed. Finally, I had to make the database offline then I dropped it. I need to create such database on my external hard disk not on my primary one. Can anyone help?!!!

Re: Database creation on an external HD

Eddy Vluggen28-Dec-15 23:39

Eddy Vluggen

28-Dec-15 23:39

Why on an external HD?

SQL Server does not support plug & play; you may loose data if the drive is removed without detaching the database. If you just want to protect the data then I'd suggest backup up to the external HD and not using it as the servers' datasource.

IIRC, then only network drives are 'disallowed'. If you want to setup the data-files to be on an external disc, you'd be interested in this[^] thread.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Cascading referential integrity.

Member 1116162522-Dec-15 21:03

Member 11161625

22-Dec-15 21:03

HI,

I want to know that while creating cascading referential integrity constraint between tables having foreign key relationships.

For e.g:-

I have two tables Employee and EmployeeLeave,now i want to use cascading referential integrity constraint

code:-

Alter Table dbo.EmployeeLeave
Add Constraint rfkcEmployeeID Foreign Key(EmployeeID)
References dbo.Employee(EmployeeID)
ON DELETE NO ACTION ON UPDATE NO ACTION

So, My question is that i can even use this Constraint at the time of Creating Table

For e.g:-

Create Table dbo.EmployeeLeave
(
EmployeeID int constraint rfkcEmployeeID Foreign Key(EmployeeID)References dbo.Employee(EmployeeID)
ON DELETE NO ACTION ON UPDATE NO ACTION
)

Is this code is correct???
Is it will work???

Thanks...

Re: Cascading referential integrity.

CHill6023-Dec-15 23:15

CHill60

23-Dec-15 23:15

What happened when you tried it?

Re: Cascading referential integrity.

Eddy Vluggen24-Dec-15 1:25

Eddy Vluggen

24-Dec-15 1:25

Member 11161625 wrote:
Is this code is correct???

Syntactic it is correct, but it will do nothing, as you instructed it. That is the default behaviour.

If you want the "leave" days to be removed from the database when the employee is removed from the database, then you'd want a cascading delete. (ON DELETE CASCADE, not "NO ACTION"). That way you can prevent getting "orphaned" records.

If you still need the leave-data from that person and don't want to wipe the records, I'd recommend "ON DELETE SET NULL", since the key would have become invalid - but then the record isn't deleted, just the key is removed - handy if you need the leave-data for statistics or other stuff.

Last, there is also a "SET DEFAULT" option; if a manager is deleted from the database, then you might want to automatically update all affected workers to some default-manager.

Member 11161625 wrote:
Is it will work???

Depends on the specs. Press F5 and see if it does what you expect. Disclaimer; various database-engines may use different keywords and not support them all.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Unusual Ways To Make Quick Money

Member 1222215021-Dec-15 20:46

Member 12222150

21-Dec-15 20:46

Federal Reserve note and no issued by a private bank in 1944 at the in world war two the Breton Woods Accord was made in world war two United States was recognized as the developing worldwide economy and by then thereto Woods accord pegged all monetary forms to the US dollars that universal exchange could be could be smoothed out and starting there for essentially Cash Camp all merchandise were evaluated in US dollar the US dollar was pegged at the at the it was picked to gold and the peg.

http://t-rexmusclefacts.com/cash-camp/[^]

plsql related issue

Member 1191972221-Dec-15 0:34

Member 11919722

21-Dec-15 0:34

create or replace FUNCTION Activation2(v_status OUT varchar,v_status1 OUT varchar)
returns setof sc_register AS $$
declare
begin
SELECT st_value into v_status FROM sc_activation_dtls WHERE si_slno = 2;
select * from sc_register ;
end;
$$
LANGUAGE 'plpgsql' VOLATILE

aftr writng above plsql functiopn in postgresql pane m getting follwing error

function result type must be record because of OUT parameters

doest it mean that we cant use output parameter and return statent togather.

since my requirement is to get some value of table through output parameters along with records of different table ..in one function only.

Thanks

Subqueries

Member 1116162519-Dec-15 17:13

Member 11161625

19-Dec-15 17:13

Hi,

I want to know that while creating suqueries in SQL Server,it is mandatory to have Primary key and Foreign key relationships between the tables???

In short:-I want to know if we create subqueries without p.k and F.k realtionships,then it will work???

What is the main purpose of Subqueries???

Thanks...

Re: Subqueries

Mycroft Holmes20-Dec-15 11:58

Mycroft Holmes

20-Dec-15 11:58

In Short: Yes

The relationship structure is an integral part of the data structure but is not required for sub queries. Sub queries are just another tool for getting your data from the database the way you want it. Like CTEs and the various joins they are all aspects of TSQL.

Never underestimate the power of human stupidity
RAH

Re: Subqueries

Richard Andrew x6420-Dec-15 15:02

Richard Andrew x64

20-Dec-15 15:02

I have often wondered this myself. Since I can perform a join simply by saying "WHERE a.field = b.field", what is the ultimate purpose of setting up a relationship?

The difficult we do right away...
...the impossible takes slightly longer.

Re: Subqueries

Wonde Tadesse20-Dec-15 15:28

Wonde Tadesse

20-Dec-15 15:28

Richard Andrew x64
what is the ultimate purpose of setting up a relationship?

Referential Integrity[^]

Wonde Tadesse

Re: Subqueries

Mycroft Holmes20-Dec-15 19:15

Mycroft Holmes

20-Dec-15 19:15

Wonde nailed it, IMHO referential integrity and constraints are the last line of defense to keep the garbage in. They also help with optimisation of course.

Never underestimate the power of human stupidity
RAH

Synchronising number of disconnected databases to a central DB.

srinandan..14-Dec-15 17:45

srinandan..

14-Dec-15 17:45

There are number of laptops installed a window application connected with local database and all these machines works in remote area where no internet connectiviy is there. we have another web application connected to a central db.the Idea to synchroze database is that every machines export there data at the end of day and save the exported file in removable disk and place all the exported file to a specific location using FTP.

My requirement is to import all the exported files into central database and vice-versa. there are around 15 table out of 100 on which need to keep the synchronization.

Please suggest.

Re: Synchronising number of disconnected databases to a central DB.

Mycroft Holmes14-Dec-15 18:52

Mycroft Holmes

14-Dec-15 18:52

There may be a method to do this using replication but I have never explored that avenue, I have used a brute force method by designing my database to used GUIDs for the primary key and therefore the tables should be mergable.

Another way is to add a machine id to each table/record so the records are unique across databases.

Never underestimate the power of human stupidity
RAH

Re: Synchronising number of disconnected databases to a central DB.

srinandan..14-Dec-15 22:20

srinandan..

14-Dec-15 22:20

Can we achieve this using Microsoft sync framework or some framework?

modified 15-Dec-15 5:07am.

Re: Synchronising number of disconnected databases to a central DB.

Mycroft Holmes15-Dec-15 11:44

Mycroft Holmes

15-Dec-15 11:44

I doubt it, I have not worked with any of them but I have never heard of of them synching database content. You would still need to identify each record uniquely across all the databases.

Never underestimate the power of human stupidity
RAH

Re: Synchronising number of disconnected databases to a central DB.

Eddy Vluggen15-Dec-15 11:53

Eddy Vluggen

15-Dec-15 11:53

There would be some examples on how to synchronize Sql Server Express/CE with Sql Server, so yes. Then again, these samples are built specifically to show some specific functionality.

Means it is no silver bullet; you may want to print the documentation and set up a test-environment.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

SQL Server deadlocks

Member 1115415113-Dec-15 6:42

Member 11154151

13-Dec-15 6:42

I'm dealing with a strange (for me) situation in SQL Server.
I'm having a process that locks the same table twice. If the same time that the first process is locking the table a second process tries to lock the same table a deadlock happens immediately. Is this normal?
I wrote a script and run it twice from different connections.
Here is the script:

SQL

begin tran myTran
create table anyTable (anyColumn varchar(10), primary key (anyColumn))

declare @exec datetime, @i int, @m varchar(2), @x int
set @exec = getdate()
set @m = substring(convert(varchar, @exec, 120), 15, 2)
set @x = convert(int, @m) + 1

print 'Before Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate())	--remove for the second process (victim)
update anyTable set anyColumn = anyColumn												--remove for the second process (victim)
print 'After Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate())		--remove for the second process (victim)

while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right(rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
	print 'Waiting. Time = ' + convert(varchar(40), @exec)
	set @exec = getdate()
end

print 'Before Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())
update anyTable set anyColumn = anyColumn
print 'After Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())

drop table anyTable

commit tran myTran

Re: SQL Server deadlocks

Kornfeld Eliyahu Peter13-Dec-15 7:17

Kornfeld Eliyahu Peter

13-Dec-15 7:17

Are you executing this exact code twice?

Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.

Re: SQL Server deadlocks

Member 1115415113-Dec-15 19:41

Member 11154151

13-Dec-15 19:41

Not this exact code twice. But similar yes. The only difference is the three lines on top which are commented for the process I would like to be the deadlock victim.
These lines:
print 'Before Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate()) --remove for the second process (victim)
update anyTable set anyColumn = anyColumn --remove for the second process (victim)
print 'After Lock anyTable for 1st time. Time = ' + convert(varchar(40), getdate()) --remove for the second process (victim)
The only alternative that I can think of is to check if the table is locked by my SPID before try to lock it for the second time.

Thanks for your posts. If can anyone come with any idea I would appreciate it.

Re: SQL Server deadlocks

Kornfeld Eliyahu Peter13-Dec-15 20:23

Kornfeld Eliyahu Peter

13-Dec-15 20:23

It was much better two post the code you have problem with - including the way you are running it...
The reason I say this, is that I took your code and immediately saw that it can not be run twice as every instance will try to create a table, and that can be a serious problem...
Other thing is the transaction - do not create a transaction if you need not (and from the sample you had here you need not, as you are updating the very same table twice)...

Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.

Re: SQL Server deadlocks

Member 1115415113-Dec-15 20:36

Member 11154151

13-Dec-15 20:36

You are right. The second process should run something like this:
begin tran myTran

declare @exec datetime, @i int, @m varchar(2), @x int
set @exec = getdate()
set @m = substring(convert(varchar, @exec, 120), 15, 2)
set @x = convert(int, @m) + 1

while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right(rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
print 'Waiting. Time = ' + convert(varchar(40), @exec)
set @exec = getdate()
end

print 'Before Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())
update anyTable set anyColumn = anyColumn
print 'After Lock anyTable for 2nd time. Time = ' + convert(varchar(40), getdate())

commit tran myTran

But the reason that I lock the same table twice is much more complicated that you think. This is not a simple process. The two locks are coming from different updates. I have just tried to make it simple.

Thanks any way.

Re: SQL Server deadlocks

Member 1115415114-Dec-15 0:00

Member 11154151

14-Dec-15 0:00

The situation is much more complex than I thougth it was.
The deadlock happened between the primary key of the table and the index of the same table. I really do not know why. A workaround that I found is to use SELECT 1 FROM EDEFADDERS WITH (TABLOCKX, HOLDLOCK) insted of UPDATE EDEFADDERS SET ID = ID.
Here is the scripts that you need to reproduced it. (I believe complete and correct!!!)
Script to create the table:

SQL

CREATE TABLE EDEFADDERS(
	id varchar(32) NOT NULL,
	adderkind int NOT NULL,
	description varchar(30) NOT NULL,
	formula text NOT NULL,
	transkind int NOT NULL,
	addedset varchar(255) NULL,
	removedset varchar(255) NULL,
	adderdecs int NOT NULL,
PRIMARY KEY CLUSTERED (id ASC) 
)

CREATE NONCLUSTERED INDEX EDEFADDERS_I1 ON EDEFADDERS (adderkind ASC)

INSERT INTO EDEFADDERS VALUES ('088162994983574692C347CE326582BD', 0, 'Πληρ. Μετρητοίς', '"Συνολική Αξία"', 2, 41, , 0)
INSERT INTO EDEFADDERS VALUES ('0DCE2805DF7E4B4C875649128C16A14C', 0, 'Εισπρ. Εμβασμ.', '"Συνολική Αξία"', 1, 24, , 0)
INSERT INTO EDEFADDERS VALUES ('1072EB880FABF8459B99EC38058B6CA1', 2, 'Εισπράξεις Γραμ.', '"Συνολική Αξία"', 1, 22, , 0)
INSERT INTO EDEFADDERS VALUES ('142F006F40F0DA4C80ACEA477E2B5662', 0, 'Εκπτώσεις Χονδρ.', '"Αξία Εκπτωσης" + "Πιστωτικό Εκπτωσης" * "Καθαρή Αξία"', 5, 70,72,80, 73,74,83, 0)
INSERT INTO EDEFADDERS VALUES ('1D4E3250AFD52343A1680A4387BA1490', 0, 'Εισπρ. Μετρητοίς', '"Συνολική Αξία"', 1, 21, , 0)
INSERT INTO EDEFADDERS VALUES ('22A60A4EC35EC14E8AD82B97A5906572', 2, 'Εισπράξεις Επιτ.', '"Συνολική Αξία"', 1, 23, , 0)
INSERT INTO EDEFADDERS VALUES ('24B3B31AD780124B888C5D935966F679', 3, 'Επιστροφές Μετρ.', '"Συνολική Αξία"', 2, 49, , 0)
INSERT INTO EDEFADDERS VALUES ('273FED73284E5E4689BAA7F913173086', 0, 'Εκπτ. Ειδών Χ.', '"Αξία Εκπτωσης Είδους"', 5, 70,72,80, 73,83, 0)
INSERT INTO EDEFADDERS VALUES ('3034E6BD63AF114A8DEB61B442C8D873', 0, 'Πωλήσεις Π.Υ. Λ.', '"Καθαρή Αξία"', 5, 84, , 0)
INSERT INTO EDEFADDERS VALUES ('349B9E68AE2D104689B6E2E67C618A8E', 0, 'Εκπτώσεις Π.Υ.Χ.', '"Αξία Εκπτωσης"', 5, 78, 79, 0)
INSERT INTO EDEFADDERS VALUES ('3708771B165CD74E81323BCBDE14CEC7', 2, 'Εισπράξεις Μετρ.', '"Συνολική Αξία"', 1, 21, , 0)
INSERT INTO EDEFADDERS VALUES ('38570F0087D3D94D838C4219E713F0FA', 0, 'Εισπρ. Πιστ.Καρτ', '"Συνολική Αξία"', 1, 26, , 0)
INSERT INTO EDEFADDERS VALUES ('3AC77B713D3FB546947FE018C7B2BF7E', 0, 'Πληρ. Εμβασμ.', '"Συνολική Αξία"', 2, 44, , 0)
INSERT INTO EDEFADDERS VALUES ('4259AB5FE2C25C4DA9C975E24F42BA4F', 0, 'Πωλήσεις Λιαν.', '"Καθαρή Αξία"', 5, 76,82, 77, 0)
INSERT INTO EDEFADDERS VALUES ('4AF5EDBA1B0BED4CB4085F3B8D567AA1', 0, 'ΠΩΛ.ΑΝΑ ΚΑΤΗΓΟΡΙ', '"Καθαρή Αξία" *(( inset ( "Πωλητής/Αντιπρόσωπος" , '1' )) AND ( "Κατηγορία Είδους" = 01 ))', 5, 70,72,74, 73,79, 0)
INSERT INTO EDEFADDERS VALUES ('4BD015E24E739C4292009DB18DA2AED3', 1, 'Σύν.Εκπτ.Πελατών', '"Αξία Εκπτωσης Πελάτη"', 5, 70,72,78, 73,74,79, 0)
INSERT INTO EDEFADDERS VALUES ('5390C0D7C85DE44797C2AF36B46FD441', 0, 'Ποσοτ. Εκπτ. Χ.', '"Αξία Εκπτωσης Τιμοκαταλόγου"', 5, 70,72,80, 73,83, 0)
INSERT INTO EDEFADDERS VALUES ('5986CE3CE649D14BA0E7DC9138DC24C9', 0, 'Πωλήσεις Χονδρ.', '"Καθαρή Αξία"', 5, 70,72,80, 73,74,83, 0)
INSERT INTO EDEFADDERS VALUES ('5A2A9016A3C57A4CB6FD3FD9D3B5E40E', 0, 'Πιστωτικά Εκπτ.', '"Καθαρή Αξία"', 5, 74, , 0)
INSERT INTO EDEFADDERS VALUES ('5AC130A23482C345B581091BA5504859', 0, 'Αθρ. Επιχ. 31', '"Καθαρή Αξία" *(( inset ( "Πωλητής/Αντιπρόσωπος" , '1 - 2' )) AND ( "Κατηγορία Είδους" = 01 ))', 5, 70,72,74, 73,79, 0)
INSERT INTO EDEFADDERS VALUES ('5B61F8A2D361014484234E50AEC99045', 0, 'Λοιπές Εξαγωγές', '"Καθαρή Αξία"', 5, 11, , 0)
INSERT INTO EDEFADDERS VALUES ('5D59097BF887F240801FFAF200B427A6', 0, 'Αγορές Χονδρικής', '"Καθαρή Αξία"', 4, 40,42, 43,44,49, 0)
INSERT INTO EDEFADDERS VALUES ('604F30B7A1CB434D9F7DADD59A9B9A74', 0, 'Εκπτώσεις Πελ. Χ', '"Αξία Εκπτωσης Πελάτη"', 5, 70,72,80, 73,83, 0)
INSERT INTO EDEFADDERS VALUES ('641899F5AFF050479F9454573841EC8E', 3, 'Πληρωμές Αντικ.', '"Συνολική Αξία"', 2, 45, , 0)
INSERT INTO EDEFADDERS VALUES ('6523E3A73BAD414EA534954D1C21106E', 0, 'Πληρ. Γραμματ.', '"Συνολική Αξία"', 2, 42, , 0)
INSERT INTO EDEFADDERS VALUES ('666D15457C526B4FBF96539E20BBE802', 2, 'Εισπρ. Εμβασμ.', '"Συνολική Αξία"', 1, 24, , 0)
INSERT INTO EDEFADDERS VALUES ('706E587CF5A1E648939391046C5FA117', 3, 'Πληρωμές Επιταγ.', '"Συνολική Αξία"', 2, 43, , 0)
INSERT INTO EDEFADDERS VALUES ('714F6D3207A8BD4BACDAD6FCB86F774B', 1, 'Σύν.Ειδικών Εκπτ', '"Ειδική Εκπτωση"', 5, 70,72,78, 73,74,79, 0)
INSERT INTO EDEFADDERS VALUES ('73C057D1EFC0C440AB42AB2E43E59ED7', 0, 'Εισπρ. Αντικατ.', '"Συνολική Αξία"', 1, 25, , 0)
INSERT INTO EDEFADDERS VALUES ('77D6C0A98A289F4F92FCBA462DC78449', 0, 'Ειδικές Εκπτ. Χ.', '"Ειδική Εκπτωση"', 5, 70,72,80, 73,83, 0)
INSERT INTO EDEFADDERS VALUES ('7CC5CEDDAB8D8746AD7B1D7E315B75DE', 0, 'ΠΩΛΗΣΕΙΣ', '"Καθαρή Αξία"', 5, 70,72,76,78,82,84, 73,74,79,83, 0)
INSERT INTO EDEFADDERS VALUES ('82A8E1B3BF46D04EB1DC0CB178FE15BB', 2, 'Εισπρ. Αντικατ.', '"Συνολική Αξία"', 1, 25, , 0)
INSERT INTO EDEFADDERS VALUES ('8F8A505724081E4AB510EEE90A4F58C1', 1, 'Σύν.Εκπτ. Ειδους', '"Αξία Εκπτωσης Είδους"', 5, 70,72,78, 73,74,79, 0)
INSERT INTO EDEFADDERS VALUES ('90499A1A8954D741A1C076735E4C4B6E', 3, 'Πληρωμές Εμβασμ.', '"Συνολική Αξία"', 2, 44, , 0)
INSERT INTO EDEFADDERS VALUES ('97CCD23F1432954EBABBE391B0945CE9', 0, 'Εκπτώσεις Λιαν.', '"Αξία Εκπτωσης"', 5, 76,82, 77, 0)
INSERT INTO EDEFADDERS VALUES ('9E0D358AECAE0A47BD0D27CE8A8FFCF3', 0, 'Εκπτώσεις Π.Υ.Λ.', '"Αξία Εκπτωσης"', 5, 84, , 0)
INSERT INTO EDEFADDERS VALUES ('A084AC5553C52544B4B053DF77817265', 2, 'Εισπρ. Πιστ. Κ.', '"Συνολική Αξία"', 1, 26, , 0)
INSERT INTO EDEFADDERS VALUES ('A0CDBA5347B1CD48B2119BE5C007F3C5', 0, 'Εισπρ. Επιταγών', '"Συνολική Αξία"', 1, 23, , 0)
INSERT INTO EDEFADDERS VALUES ('A329C05004756C4EBAACA1B5F269DE1F', 0, 'Αθρ. Επιχ. 32', '"Καθαρή Αξία" *(( inset ( "Πωλητής/Αντιπρόσωπος" , '1 - 2' ))*( inset ( "Κατηγορία Είδους" , '1 - 2' )))', 5, 70,72,74, 73,79, 0)
INSERT INTO EDEFADDERS VALUES ('B175031C0CA3364193E39C41FC29A262', 2, 'Επιστροφές Μετρ.', '"Συνολική Αξία"', 1, 29, , 0)
INSERT INTO EDEFADDERS VALUES ('B54944B81669EF498F3E2D55B5F8ECD5', 1, 'Σύν.Ποσοτ. Εκπτ.', '"Αξία Εκπτωσης Τιμοκαταλόγου"', 5, 70,72,78, 73,74,79, 0)
INSERT INTO EDEFADDERS VALUES ('B6527AFBC4B345469C4B81D1B768504F', 0, 'Πληρ. Επιταγών', '"Συνολική Αξία"', 2, 43, , 0)
INSERT INTO EDEFADDERS VALUES ('BE5E6E822BF0D946918436C3F9F28737', 0, 'Αγορές Παρ.Υπηρ.', '"Καθαρή Αξία"', 4, 46, 47, 0)
INSERT INTO EDEFADDERS VALUES ('C5D6BFD587A11948949446161E501015', 3, 'Πληρωμές Μετρητ.', '"Συνολική Αξία"', 2, 41, , 0)
INSERT INTO EDEFADDERS VALUES ('CABA8202B260F046945AD56CCAD51A56', 0, 'Εκπτ. Τρ.Πληρ. Χ', '"Αξία Εκπτωσης Τρόπου Πληρωμής"', 5, 70,72,80, 73,83, 0)
INSERT INTO EDEFADDERS VALUES ('CDEAFE4EF039DD4A9A7721A35BC949AA', 1, 'Σύν.Εκπτ.Τρ.Πληρ', '"Αξία Εκπτωσης Τρόπου Πληρωμής"', 5, 70,72,78, 73,74,79, 0)
INSERT INTO EDEFADDERS VALUES ('CE97BE8509223145B274049AE6EABBF4', 0, 'Πωλήσεις Π.Υ. Χ.', '"Καθαρή Αξία"', 5, 78, 79, 0)
INSERT INTO EDEFADDERS VALUES ('D051DCEB86DFAB4BA3B564A1B4570CED', 3, 'Πληρωμές Γραμ.', '"Συνολική Αξία"', 2, 42, , 0)
INSERT INTO EDEFADDERS VALUES ('EC08552E3FDBA14189D39418D5571545', 0, 'Εισπρ. Γραμμ.', '"Συνολική Αξία"', 1, 22, , 0)
INSERT INTO EDEFADDERS VALUES ('FEB6C6CE84571F4A98558A515A822C47', 0, 'Πληρ. Αντικατ.', '"Συνολική Αξία"', 2, 45, , 0)

Script to run it twice (simultaneously) that will produce the deadlock:

begin tran myTran
 
declare @exec datetime, @i int, @m varchar(2), @x int
set @exec = getdate()
set @m = substring(convert(varchar, @exec, 120), 15, 2)
set @x = convert(int, @m) + 1
 
print 'Before Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
update EDEFADDERS set ID = ID
print 'After Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
 
while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right('00' + rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
	set @exec = getdate()
end
 
print 'Before Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
update EDEFADDERS set ID = ID
print 'After Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
 
commit tran myTran

Script to workaround the problem:

begin tran myTran
 
declare @exec datetime, @i int, @m varchar(2), @x int
set @exec = getdate()
set @m = substring(convert(varchar, @exec, 120), 15, 2)
set @x = convert(int, @m) + 1
 
print 'Before Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
SELECT 1 FROM EDEFADDERS WITH (TABLOCKX, HOLDLOCK)
print 'After Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
 
while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right('00' + rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
	set @exec = getdate()
end
 
print 'Before Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
SELECT 1 FROM EDEFADDERS WITH (TABLOCKX, HOLDLOCK)
print 'After Lock EDEFADDERS for 2nd time. Time = ' + convert(varchar(40), getdate())
 
commit tran myTran

Sorry to anyone I mislead.

Re: SQL Server deadlocks

Richard Deeming14-Dec-15 2:50

Richard Deeming

14-Dec-15 2:50

Member 11154151 wrote:
while datediff(ms, @exec, convert(datetime, substring(convert(varchar, @exec, 120), 1, 14) + right(rtrim(ltrim(convert(varchar, @x))), 2) + ':00.000')) > 0
begin
print 'Waiting. Time = ' + convert(varchar(40), @exec)
set @exec = getdate()
end

Rather than sitting in a tight loop waiting for a particular time, it would be better to use WAITFOR[^]. That will let SQL free up the processor to do other things, rather than constantly querying the current time and shouting "are we nearly there yet?" until the target time arrives.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: SQL Server deadlocks

Member 1115415116-Dec-15 2:28

Member 11154151

16-Dec-15 2:28

The answer to my question was given to me by someone from Microsoft.
The reason why this deadlock happens is that the optimiser is choosing the second index for this SQL:
Update anyTable set anyColumn = anyColumn mak
So, it puts a lock in order to do the update and then SQL server is putting an exclusive lock to the primary key for the update and release the first lock of the second index.
At this time the second process is putting a lock for the second index and waits for the 1st process to finish in order to get an exclusive lock for the primary key.
The 1st process now is trying to reupdate the same table and to put a lock for the second index but this is already locked by the 2nd process (who waits the 1st).
So, the deadlock is inevitable.

Last Visit: 31-Dec-99 18:00 Last Update: 18-Apr-24 13:29

Refresh

ᐊ Prev 1...116 117 118 119120121 122 123 124 125 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.