This question is locked. New answers and comments are not allowed.
How can I query data from database including dirtyobjects in transaction?
I have table UsersInRoles that have column (UsersInRoleId PK bigint, UserId FK bigint, RoleId FK bigint)
I have function to insert multiple records within 1 transaction for example...
Begin Transaction
INSERT INTO UsersInRoles(UsersInRoleId, UserId, RoleId) VALUES (1,1,1)
INSERT INTO UsersInRoles(UsersInRoleId, UserId, RoleId) VALUES (2,1,2)
INSERT INTO UsersInRoles(UsersInRoleId, UserId, RoleId) VALUES (3,1,3)
Commit Transaction
This function works fine with correct data but there is problem for a case below...
Begin Transaction
INSERT INTO UsersInRoles(UsersInRoleId, UserId, RoleId) VALUES (1,1,1)
INSERT INTO UsersInRoles(UsersInRoleId, UserId, RoleId) VALUES (2,1,1)
Commit Transaction
The first record is inserted correctly but second record should not be inserted because UserId and RoleId is duplicated with the first record so I have created validation method to check data before saving in each record however the problem is I can query data from database only but it is not included dirty objects in transaction. How can I query in 1 time that include data from database and dirty objects in transaction? Could you please provide me an example or how to deal with this issue? I am not sure I need to set isolation level to READ_UNCOMMITTED or not? Thank you in advanced.
I have table UsersInRoles that have column (UsersInRoleId PK bigint, UserId FK bigint, RoleId FK bigint)
I have function to insert multiple records within 1 transaction for example...
Begin Transaction
INSERT INTO UsersInRoles(UsersInRoleId, UserId, RoleId) VALUES (1,1,1)
INSERT INTO UsersInRoles(UsersInRoleId, UserId, RoleId) VALUES (2,1,2)
INSERT INTO UsersInRoles(UsersInRoleId, UserId, RoleId) VALUES (3,1,3)
Commit Transaction
This function works fine with correct data but there is problem for a case below...
Begin Transaction
INSERT INTO UsersInRoles(UsersInRoleId, UserId, RoleId) VALUES (1,1,1)
INSERT INTO UsersInRoles(UsersInRoleId, UserId, RoleId) VALUES (2,1,1)
Commit Transaction
The first record is inserted correctly but second record should not be inserted because UserId and RoleId is duplicated with the first record so I have created validation method to check data before saving in each record however the problem is I can query data from database only but it is not included dirty objects in transaction. How can I query in 1 time that include data from database and dirty objects in transaction? Could you please provide me an example or how to deal with this issue? I am not sure I need to set isolation level to READ_UNCOMMITTED or not? Thank you in advanced.