regarding conflict resolution - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

regarding conflict resolution

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2008-11-16 : 10:49:04

hi,

i have a question regarding avoiding conflicting or creating two documents with the same docno at the same time what is the best choice to avoid such conflict

we have an application i made profiler trace they have this type of solution manual lock tables ex:

SELECT COUNT(LOCK_TABLENAME) FROM OAEFILE_LOCKTABLES WHERE LOCK_TABLENAME ='OAEFILE_CABINET' AND CONVERT(varchar, TRANS_DATE, 102) < CONVERT(varchar, GETDATE(), 102)
go

INSERT INTO OAEFILE_LOCKTABLES(LOCK_TABLENAME,TRANS_DATE) VALUES ('OAEFILE_CABINET','2008/11/16 15:40:52')
go

SELECT CABINET_TYPE,KEYCOL_NAME,DOC_COUNTER FROM OAEFILE_CABINET WHERE APPID='1' AND CABINETID='8'
go
exec sp_reset_connection
go
UPDATE OAEFILE_CABINET SET DOC_COUNTER='80' WHERE APPID = '1' AND CABINETID = '8'
go
DELETE FROM OAEFILE_LOCKTABLES WHERE LOCK_TABLENAME ='OAEFILE_CABINET'
go

INSERT INTO FAX_Main(DOCNO,INDOCNO,DOCDATE,DOCDATE_HJ,YEAR,TR_DUE_DATE,TR_DUE_DATE_HJ,ASSIGNTO_RECORDID,REMARKS_AR,NUM_FIELD7,NUM_FIELD8,CHAR_FIELD8_AR,CHAR_FIELD9_AR,CHAR_FIELD1,CHAR_FIELD9,FORWARD_DATE,CHAR_FIELD1_AR,CONFID_ID,CLOSE_DATE,ASSGNCNTCT_ID,SUBJECT,DOC_ORIGN_SOURCE,STATUS_ID,PRIORITY_NO,USER_ID,IP,ISHAAR_YEAR,CHAR_FIELD3_AR,CAT_ID,SUBCAT_ID,CHAR_FIELD6_AR,CHAR_FIELD7_AR,FORM_FLAG) VALUES ('541', '79', '2008/11/16 15:40:52', '18/11/1429 15:40:52', '1429', '2008/11/16', '18/11/1429 15:40:52', '1', NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'1',NULL,NULL,'test','1','1','1','2','128.1.200.41','92083','92084','92085','92086','92087','92088','304#305#306#TEXT92087')
go

IF @@TRANCOUNT > 0 COMMIT TRAN

what if there were already a lock exist in that table what would the resolution be? because i tried to insert a lock manualy then i tried to create a new document it didnt give any message it just took some more time then created the document

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-16 : 11:50:04

it waits until previously held lock is released and then assumes the lock to perform the reqd functions